Working with large and negative hours (numbers) in Excel

[Pete Wright]

Hello!

I've been struggling with my employee time sheet table for some time now and finally came up with something usable.
The only point left is dealing with negative and big times.

I tried different attempts to get that done, with no success. Excel just doesn't understand negative times (well, somehow logical, since there is nothing like -1:00 PM or -13:00 o'clock) and hours greater than 24:00 (= 1 d).

Just to give an example, let's assume something like this:

• O (sum overtime hours) = 21:45
• A (sum absence hours) = 30:30
• E (sum overtime - sum absence) = -08:45

Now we have 2 problems in just one calculation:

• 30:00 hours in Excel result in displaying 06:00 (because 30 h = 24 h + 6 h)
• -10:00 hours is not displayed at all (just a bunch of hashtags: #######...)

The only solution I found is "converting" the times to regular numbers before doing any calculations:

• 30:30 => 30.50 / 21:45 => 21.75
• 21.75 - 30.50 = -8.75

and then take the absolute value and convert it to simple text:

E = "-" & |O - A| ="-" & TEXT(ABS(O-A)/24, "hh:mm") => -08:45​

This is ridiculous.

Isn't there any other way to deal with such times?

 

[GraH - Guido]

What you call big times is a format like [H]:mm:ss. Excel will show 30:45:20.
Negative times do not exist. If E is net overtime then try = MAX(0, O-A).
Add a column "Negative Hours" and go with = ABS(O-A) or A-O.

 

[vletm]

Pete Wright
> Keep times as times
> Hours greater than 24hrs ... format cell as [h]:mm
> 'negative hours' ... solve as positive -- and -- use later as negative

 

[Pete Wright]

Hi! I have been busy the past weeks and just wanted to thank you both for your Help.

I solved the problem with some helper cells (absolute value of O-A / A-O).

 

[Peter Bartholomew]

Pleased you have the solution you require. A few further thoughts:
If you want to retain the time duration format [h]:mm rather than decimal hours, a useful function to partner
= ABS(O-A)
would be
= SIGN(O-A).
From there, what is visible on the worksheet can be changed by conditional formatting based on the ±1 and number formatting
"Additional";"Shortfall";"-"
to show text in place of ±1.

[the green formatted text shows the actual formula and content of the cells displayed in red.
In Excel, what you have is not necessarily what you see]

 

[Pete Wright]

If you want to retain the time duration format [h]:mm rather than decimal hours, a useful function to partner
= ABS(O-A)
would be
= SIGN(O-A).
From there, what is visible on the worksheet can be changed by conditional formatting based on the ±1 and number formatting
"Additional";"Shortfall";"-"
to show text in place of ±1.

Very nice! Never thought of something like this! Wow!
It's very good to have other peoples point of view on a problem. If I try to find a solution on my own, I'll be most likely stuck in one special way of thinking and so not going any further.
Many thanks for your "method"!

In Excel, what you have is not necessarily what you see]

I know, I use Conditional Formatting so much