time calculation, 24:00 - 16:00 = 8

[DaveB]

Greetings,

Ok, I have two cells formated as time (13.00). I am attempting to subtract
the end time from the start time and put the product in a cell formated as a
number with two decimal places (16:00 - 8:00 = 8.00). No problem here.

But when I use 24:00 is displays 00:00. Hence 24:00 reads as 00:00 - 16:00 =
-16.00. How can I get Excel to display 24:00 as 24 hundred hours so the
formula can use 24:00 in the calculation.

thanks in advance

 

[Ron Rosenfeld]

Greetings,

Ok, I have two cells formated as time (13.00). I am attempting to subtract
the end time from the start time and put the product in a cell formated as a
number with two decimal places (16:00 - 8:00 = 8.00). No problem here.

But when I use 24:00 is displays 00:00. Hence 24:00 reads as 00:00 - 16:00 =
-16.00. How can I get Excel to display 24:00 as 24 hundred hours so the
formula can use 24:00 in the calculation.

thanks in advance

When I enter those values in A1 and B1, even though B1 displays as 00:00, the
formula still calculates the proper value.

Did you really enter 24:00 directly into the cell?
What does the formula bar show when you click on the cell?

One general way around similar problems is this formula:

=B1-A1+(A1>B1)

--ron

 

[John]

Hi Ron
You're right, I directly entered and it's really 12.AM not PM
Please disregards my post
John

 

[David Biddulph]

.... or =MOD(B1-A1,1)
--
David Biddulph

When I enter those values in A1 and B1, even though B1 displays as
00:00, the formula still calculates the proper value.

Did you really enter 24:00 directly into the cell?
What does the formula bar show when you click on the cell?

One general way around similar problems is this formula:

=B1-A1+(A1>B1)

--ron

 

[DaveB]

the product needs to be in a number format so that each day It can be added
for total hours worked for multiple days. If I format the product cell as
time it works, BUT, the idea is to format the product call as a number for
other use in other calculations.

if i use the following formula,
=(HOUR(b1)+(MINUTE(b12)/60))-(HOUR(a1)+(MINUTE(a1)/60)) to calculate time -
time it works for all other time calculations (i.e. 16:00 - 08:00 = 8.00)
were the time cells are formated as time so that it displays as such, and the
product cell is formated as a number.

If you enter 23:59 for 24:00 it works (as long as the start time is enter as
15.59).

To sum it up.

cell a1 formates as time 13.30 is... 8.00
cell b1 fromated as time 13.00 is... 16.00
cell c1 formated as a number (2 dec places is
....=(HOUR(B1)+(MINUTE(B12)/60))-(HOUR(A1)+(MINUTE(A1)/60))