When subtracting times in Google Sheets sometimes it fails and the value is incorrect

Question

I have two fields in my Google Sheets spreadsheet and a third cell that subtracts the difference between them. Most of the time it works fine but sometimes it fails.

The way it fails is I believe that it interprets the time of one cell as a different day than the time of the next cell. IIRC it seems to have problems when going from AM to PM. Again, most of the time it works.

The formula is:

=(D:D-C:C)*24

The value in cell one and cell two that break it (it is 14:19 when I enter it):

C	D	=(D:D-C:C)*24
10:00	12:00	2
1:15	2:15	22

When I try to fix it, and enter the 24hr time, it stays stuck.

My question is, what do I do to fix this?

If Google Sheets thinks that time entered is from the previous day or from the next day, is there a way to enter the correct day or time into the time field?

OR is this a known issue and I need to redo my formula?

I remember reading that you can make a field (a cell) of a certain type. How do I know what type the cell is currently? I looked in Google Sheets and it doesn't say it anywhere. Would that prevent this issue.

If I enter the full date and time into the field the formula field throws an error "#VALUE".

UPDATE:
So I edited my formula and removed the *24 part of it and now it's showing the correct time difference for the fields that were broken.

=(D:D-C:C)

IIRC someone said I needed format my C and D columns so that it all works. Maybe that has something to do with it but I can't find any information about the type of a cell!

Usually, if you have an option selected out of multiple options there's a checkmark next to the selected option. In Google Sheets there there's nothing selected:

Answer 1

Within 24 Hours

If the time elapsed is always less than 24 hrs, then you can work with only times in the cells and use the TIMEVALUE function. When calculating the difference in time values, sheets will assume a start time that is less than the end time to be on the same day, and conversely, when the start time is larger than the end time, sheets will assume it to be on the previous day.

=TIMEVALUE(B4-A4)*24

Formula 1

Cell Value (Time)

DATETIME

If you include the full date including time instead of simply the times, you will avoid errors and your time can span any number of days.

=(B4-A4)*24

Formula 2

Cell Value (DateTime)

Or alternatively if it is easier to have the date and time in separate columns, you can first add them together

=((C4+D4)-(A4+B4))*24

Alternative Formula 2

Cell Value (Date)

Cell Value (Time)

Answer 2

For some reason, the fields in rows C and D do not show any format as being selected (including Automatic).

If I set the fields to Duration, then it mostly appears to work as desired so far.

AM to PM

PM to PM

PM to AM (fails)

PM to AM fails.

I can live with that. If it is PM to AM then maybe I can start a new row. What I mean is stop the time at midnight and then create a new row with 0:00 and continue from there.