Negative Time

[voisey]

Hi Guys & Gals
I know that there is a lot talked about time and some very usefull information already here on the site. However, I have a minor problem with Excel and negative time. I have a time sheet which is OK with everything except when confronted with a negative value. Each week we have to work 36.5 hours, but we work a flex system. This means that we are allowed to carry forward a negative time balance to the following week. The time sheet I use works fine so long as the total at the end of the week equals or is more than the 36.5 standard week. What the time sheet does is to add the totals daily and at the end of the week subtract this total from the standard week - sometimes resulting in a negative value. When Excel calculates a negative it just gives me #####################.... and on and on.
Is there a way around this? As I said earlier this is just a minor problem
Thanks
Voisey

 

[voisey]

Sorry What I really meant to ay at the end is that the standard week is then subtracted from the time worked and the excess carried forward to the following week

 

[xlbo]

Excel will not handle negative times.

Best way round is to convert to a positive time and reverse the calculation

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[voisey]

Geoff - Thanks for quick response. Doesn't want to work for me. Most weeks the time worked will be greater than the standard week - its only occasionally that this will be reversed. I've tried your suggestion but then when I should get a positive number I get #########
As I said it's only a minor problem as I just have to 'manually' adjust when the negative values appear
Thanks again

 

[Loomah]

Been there, done this! Numerous times!!

Change your timesheet file to 1904 Date System
TOOLS>OPTIONS-CALULATION/Workbook Options

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[xlbo]

voisey - with Standard Hours in A1 (eg 36:30:00) and worked hours in A2 (eg 37:00:00), the following will give you the time differential:

=IF(A2-A1<0,ABS(A2-A1),A2-A1)

You may then SUBTRACT this figure from the standard hours figure for the next week rather than ADDING it

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[voisey]

Geoff - Simple when you know how.
Thanks again for you help

 

[voisey]

loomah - I've tried your suggestion as well - great!!
voisey

 

[Loomah]

xlbo
I'm intrigued by your suggestion and I'm feeling a little dense now. Using the times you have used in your example the result is +30mins. If the hours worked is changed to 36:00:00 the result is still +30mins.

How can you tell whether more or fewer hours than required have been worked

I'm just thinking out loud (so to speak)!! I'l still be using A1-A2 coz I know it works!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[xlbo]

yeh - my solution was only part complete. The next formula to determine the hours for next week would have to test the original result also to determine whether to add or subtract the time differential eg

Standard Hours entered into A1

Formula 1 - time differential - entered into C1:
=IF(A2-A1<0,ABS(A2-A1),A2-A1)

Formula 2 - Hours required for Next Week - entered into C2:
=IF(A2-A1<0,A1-C1,A1+C1)

Hope that clarifies...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[firefytr]

Right, using ABS() will give you the positive, and if you do not know where the neg/pos values will be, it won't do you much good. But if you format the values as a number instead of a time (because the VALUE is still good, it's just the FORMAT showing the ###### signs) then you'll still see the correct value. Using a Number format will show your positive/negative times adequately and they can still be used in calculations.

-----------
Regards,
Zack Barresse

 

[xlbo]

Good thinking Zack - guess it just depends if the sheet needs to be formatted for viewing or not...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question