Subtraction of Negative Time for Comp Hours.

[Ascurlock2]

I've been attempting to create an excel book that keeps track of my working hours. Any time I work over 8 hours is banked as "calendar hours" that I use effectively as comp time. I have the sheet mostly worked out, except that when I take a day off I have it calculate as negative 8 hours. I then attempt to have it subtract it from my total comp time, but it gives me a #Value! Error. Any ideas?

Thanks.

[Ascurlock2]

I've attached the workbook.

[Pepe Le Mokko]

I don't see any errors in your sheet?

[Ascurlock2]

My apologies, uploaded the wrong one. It should be the correct one now.

[Pepe Le Mokko]

In col D and subsequent, do not use the TEXT function just =C2-B2 ( and also the other columns)
and format as h:mm

[MrShorty]

You are using a TEXT() function in columns D and F, so those values are all text strings (not real date/time numbers). Excel cannot interpret the text string "-8:00" as a number so the =number+text computation in column G fails.

I would avoid converting numbers to text using the TEXT() function. If I change columns D and F to remove the TEXT() function, then I would expect column G to execute without error.

Column D =C2-B2
column F =D2-E2

You can format columns D and F to show elapsed time with number formatting (use the same number format code you are using in column G or a code like [h]:mm.

Does that help?

[Ascurlock2]

That was it, thank you both!!!