Time Keeping

[Guest]

I'm trying to make a time sheet for myself but my time keeps coming up
negative.
in cell a1 i have 9:00 AM (with the cell formated as time h:mm PM) and cell
b1 5:00 PM (with the cell formated as time h:mm PM) and cell c1 formated as
number (with formula (A1-B1)*24 . I'm getting 8 hours but it is coming out
negative.
so my cells look like this
9:00 AM 5:00 PM -8.00
What am i doing wrong? How can i get a positive number?

 

[Niek Otten]

=(B1-A1)*24

 

[Guest]

Subtract the start time from the end time, rather than the other way around:
=(b1-a1)*24.

 

[David McRitchie]

Hi _____,
Actually if a time interval went through midnight you would get a negative number even
with the "correct" formula (end-start). So you would want to add 24 hours (= 1 day)
in the calculation.
=((B1-A1)+(A1>B1))*24 format as a number (not as time), or as
=(B1-A1)+(A1>B1) format as h:mm (as an Excel time, [h]:mm for a total)
The A1>B1 is a logical it will return 0 or 1
Excel dates and times are the number of days (and fractional days) pas a reference date.

For more information on Date and Time see
http://www.mvps.org/dmcritchie/excel/datetime.htm
http://www.cpearson.com/excel/datetime.htm