Time Formula

J

Joe

I have a two times (ex 8:30 AM and 5:30 PM) I want to
figure out the number of hours from the start of one time
to the end of the other time. What formula do I use?
 
R

Richard O. Neville

The formula is =[cell showing 5:30 PM]-[cell showing 8:30 AM]. Format the
answer as 0.00 and the formula will show hours and fractions thereof (e.g. 4
1/2 hours will show as 4.50).
 
K

Klatuu

It will take three formulas. Timevalue, Hour, and Minute. Each of Your times
must be in Text format as in your example.
=Timevalue("8:30 AM") will return 0.354166667
=timevalue("5:30 PM") will return 0.729166667
Subtract your start time from your end time to get the interval (0.375)
Use the Hour function on the interval
=Hour(0.375)
In this case the result will be 9

If there are Minutes in the result, you will do the same with minute.
=minute(0.375)
In this case it will return 0

Now, if the two times cross midnight, unless someone else has a slicker
idea, It will be necessary to break it into two steps by figuring the time
until 11:59 PM and adding it to the time in the next day.
 
M

Myrna Larson

Hmmm... I followed your instructions explicitly. I see 0.375 in the cell, not
9.00. Times are expressed as a fraction of a day. If you want to see 9.00, you
have to convert from days to hours: multiply by 24, i.e

=([cell showing 5:30 PM]-[cell showing 8:30 AM])*24



The formula is =[cell showing 5:30 PM]-[cell showing 8:30 AM]. Format the
answer as 0.00 and the formula will show hours and fractions thereof (e.g. 4
1/2 hours will show as 4.50).

Joe said:
I have a two times (ex 8:30 AM and 5:30 PM) I want to
figure out the number of hours from the start of one time
to the end of the other time. What formula do I use?
Click to expand...
Click to expand...
 
Top