J
james
I am trying to set up a spreadsheet to record staff flex-time. Formating to
record time produces errors when negative are introduced.
record time produces errors when negative are introduced.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
H
Harald Staff
Hi
Set your spreadsheet to 1904 calendar (Tools > Options menu) to display
negative time. (Note that dates will shidt 4 years 1 days off by doing
this).
HTH. Best wishes Harald
Set your spreadsheet to 1904 calendar (Tools > Options menu) to display
negative time. (Note that dates will shidt 4 years 1 days off by doing
this).
HTH. Best wishes Harald
D
David McRitchie
Hi James,
If you are trying to obtain the hours through midnight, you would have
to add 24 hours (24 hours = 1 day) to the end time.
C2: 22:00
D2: 06:00
E2: =D2-C2+(C2>D2)
C2>D2 is a logical test it returns True or False
or in arithmetic 1=True 0=False
More information on Date and Time in
http://www.mvps.org/dmcritchie/excel/datetime.htm
http://www.cpearson.com/excel/datetime.htm
If you are trying to obtain the hours through midnight, you would have
to add 24 hours (24 hours = 1 day) to the end time.
C2: 22:00
D2: 06:00
E2: =D2-C2+(C2>D2)
C2>D2 is a logical test it returns True or False
or in arithmetic 1=True 0=False
More information on Date and Time in
http://www.mvps.org/dmcritchie/excel/datetime.htm
http://www.cpearson.com/excel/datetime.htm
B
Bob Phillips
It is still a correct value even if it doesn't show as such, so any further
calculations using that value will be correct.
If it is just to see it correctly, you could force it like so
=IF(B1-A1<0,"-","")&TEXT(ABS(B1-A1),"hh:mm:SS")
--
HTH
RP
(remove nothere from the email address if mailing direct)
calculations using that value will be correct.
If it is just to see it correctly, you could force it like so
=IF(B1-A1<0,"-","")&TEXT(ABS(B1-A1),"hh:mm:SS")
--
HTH
RP
(remove nothere from the email address if mailing direct)