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

D

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
 
R

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
 
J

John

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

David Biddulph

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

Ron said:
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
 
D

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))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top