Displaying calculated times - half-hours are not calculating...

[jacob]

Good morning!

In A1 I have a time, displayed as hh:mm AM/PM
In A2 I have a time, displayed as hh:mm AM/PM
In A3 I have a non-time number (0, 0.5, or 1.0)
In A4 I have the formula: =(TEXT(C6-B6,"h"))
In A5 I have the formula: =A4-A3

The end result is the difference in times between A2 and A1, minus the
0, 0.5 or 1.0 from A3 to get a total number of hours. The problem is,
A4 will not recognize 1/2 hour times (i.e., A1=9:00 am, A2=1:30 pm
A4=4) And A5 does not recognize the A3 numeral if it is 0.5.
I suspect it has something to do with only displaying "h" but I can't
seem to fix it. It also may have something to do with the Format that
the cells are set to display. (I've tried Customer, hh:mm, and with
the above formulas this definitely does not work).

So if
A1=9:00 am
A2=1:30 pm
A3 =0.5

I would like
A4=4.5
A5=4

I hope my descriptions make sense. If you can offer a solution it
would be highly appreciated!

Thank-you,
Jacob

 

[Roger Govier]

Hi

Excel stores times as fractions of a day so to convert to decimal hours
try in A4
=(A2-A1)*24
Format the cell as General or Number
A5 =A4-A3