Adding Subtracting Time

  • Thread starter Thread starter Keht
  • Start date Start date
K

Keht

Hello, I need to make a time sheet work sheet. My problem is I want to
Subtract time to a number value... I.E. 12:00PM TO 8:00PM = 8 how would i
do this?
 
Since XL stores times as fractional days (e.g., 06:00 = 6/24 = 0.25),
you can multiply by 24

=(B1-A1)*24

Note however, that if the times span midnight, the "later" time will be
less than the earlier time. In that case you need to add 24 hours (=1).
You can use XL's implicit coerciion of TRUE/FALSE to 1/0:

=(B1-A1+(B1<A1))*24

or you can use XL's MOD() function:

=MOD(B1-A1,1)*24


For more on time arithmetic, check out

http://cpearson.com/excel/datetime.htm#AddingTimes
 
Keht said:
Hello, I need to make a time sheet work sheet. My problem is I want to
Subtract time to a number value... I.E. 12:00PM TO 8:00PM = 8 how would i
do this?
Click to expand...

you could try "=hour(A2)-hour(A1)". If the times will have minutes, then you
might have to do some extra converting using "minute()" as well.
 
Back
Top