Negative Time Calculation

  • Thread starter Thread starter John
  • Start date Start date
J

John

I have the following formula that calculates the difference between an
employees clocked time and their amended times if manual interventations
have been made

=IF(K63="","",IF(AND(K63>0,K60=""),(K63-K59),(K60-K63))) <the cell is
formatted as [h]:mm>

The formula returns a ##### value when the above value is negative, however
I want to see the negative hours shown as -2:32 etc

Is this possible

Thanks
 
I believe that changing my base date to 1904 works, but that can cause
serious grief

Any other work around
 
FYI

I've changed the format to Number with 2 decimals and multipled the result
by 24 - seems to work

John said:
I believe that changing my base date to 1904 works, but that can cause
serious grief

Any other work around


John said:
I have the following formula that calculates the difference between an
employees clocked time and their amended times if manual interventations
have been made

=IF(K63="","",IF(AND(K63>0,K60=""),(K63-K59),(K60-K63))) <the cell is
formatted as [h]:mm>

The formula returns a ##### value when the above value is negative,
however I want to see the negative hours shown as -2:32 etc

Is this possible

Thanks
Click to expand...
Click to expand...
 
John,

Seems convoluted to have to do, but

=IF(K63="","",IF(AND(K63>0,K60=""),ABS(K63-K59),ABS(K60-K63)))
in your cell

=OR((K60-K63<0),(K59-K63<0))
in conditional formatting (make red if negative)

and you can keep your time formatting...

Beege
 
Back
Top