G Guest Apr 15, 2005 #1 I am trying to set up a spreadsheet to record staff flex-time. Formating to record time produces errors when negative are introduced.
I am trying to set up a spreadsheet to record staff flex-time. Formating to record time produces errors when negative are introduced.
H Harald Staff Apr 15, 2005 #2 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
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
D David McRitchie Apr 15, 2005 #3 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
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
B Bob Phillips Apr 15, 2005 #4 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)
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)
