Excel negative time / date

  navvyman 18:32 24 Sep 07
Locked

I may have my stupid head on but:
Excel displays negative dates as an apparently infinite row of hash marks.
I have a spreadsheet that calculates hours worked by deducting start time from finish time - all formatted as hours and minutes xx:xx.
It then subtracts standard hours from hours worked to give extra hours worked - also xx:xx. So far so good, but it all goes to pot when the employee works less than standard hours and all I get is ### ad infinitum.
Any suggestions for a simple solution - I can use an IF function but need to explain it to non IT literate users (or I can use an IF and hide the cell, I guess)
Thanks

  VoG II 19:25 24 Sep 07

You can try Tools > Options and change to the 1904 date system which I believe supports negative times.

The alternative is to use a formula like

=MOD(B1-A1,1)

which will return a positive time even if A1 > B1 - you could use an extra cell to indicate whether that condition was true.

  navvyman 19:37 24 Sep 07

Thanks VoG - reliable and accurate as ever - the 1904 date format works a treat - and they understand excel dates so not too much to explain.

(I'll play with the MOD function later for fun!)

This thread is now locked and can not be replied to.

Hands-on: Samsung Galaxy S8 review

1995-2015: How technology has changed the world in 20 years

Samsung's beautifully designed Galaxy S8 makes for better VR experiences too

47 iPhone camera tips to help you take better photos