Excel negative time / date

  navvyman 18:32 24 Sep 2007
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 2007

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 2007

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.

Elsewhere on IDG sites

Alienware 17 R4 2017 review

These brilliant Lego posters show just what children's imaginations are capable of

Mac power user tips and hidden tricks

Comment réinitialiser votre PC, ordinateur portable ou tablette Windows ?