Cell turns ####### when time turns negative

[Flopem123]

Hi all

I'm trying to calculate a number of cells, which all have a time in them.

Contracted	37:00:00
Hours Worked	5:27
Credit from Previous Week	3:25
Debit from Previous Week	0:00
Total	28:08

The time calculation is fine when the time is positive but as soon as the time goes below zero I get ######## in the total cell. I did find a formula (below) which worked for subtracting one time from another but as I have a third cell which needs to be included in the calculation I cant seem to get it to work. I have tried adjusting the formula but nothing seems to work.

=IF(R2-R3<0, "-" & TEXT(ABS(R2-R3),"hh:mm"), R2-R3)

Hope you can help

Thanks

 

[joeu2004]

Try:

=IF(R2-SUM(R3:R5)<0, TEXT(SUM(R3:R5)-R2, "\-[hh]:mm"), R2-SUM(R3:R5))

formatted as Custom [hh]:mm and Horizontal Alignment right.

The square brackets around [hh] is recommended for any time calculation, just in case the result is 24 hours or more, as your example is.

But beware that the "negative" result is text, not a number. You cannot reference it directly in calculations in other cells. LMK if that might be a problem. There is a solution.

IMHO, resist any suggestion to set the 1904 Date System. Although that does allow for negative time as a number, it might screw up other things on PCs, especially in an existing workbook and when copy-and-pasting dates between workbooks.

 

[joeu2004]

=IF(R2-SUM(R3:R5)<0, TEXT(SUM(R3:R5)-R2, "\-[hh]:mm"), R2-SUM(R3:R5))

I just noticed that R5 is labeled "debit", which might be added, not subtracted. So you might write:

=IF(SUM(R2,R5)-SUM(R3:R4)<0, TEXT(SUM(R3:R4)-SUM(R2,R5), "\-[hh]:mm"), SUM(R2,R5)-SUM(R3:R4))

Although you do not need to use the SUM function here, I use it to emphasize the "need" (best practice, IMHO) to collect terms to add and terms to subtract.

And of course, it would be ever simpler if SUM(R2,R5)-SUM(R3:R4) were calculated in a "helper" cell (possibly hidden).

 

[Flopem123]

Hi Joe, The second formula worked great. Your correct I have a credit and a debit field and the debit it added rather than subtracted.

I have seen a number of posts regarding the 1904 option but was reluctant as this timesheet will be used by about 10 people.

Thanks for your help