1. ## Negative Time (All)

When I try to subtract 2:30 from 4:15, I sould get -1:45.
However, Excel returns "######".

Is there a way I can get negative time values to work?

Rlay

2. ## Re: Negative Time (All)

First, since 4:15-2:30 is 1:45, not -1:45, I will assume that what you meant is 2:15-4:30.

Excel does not recognize negative times. So, you can not accomplish what you want using Excel time values. Since negative times are also not physically possible, can you explain what you are trying to do so we can see if there is some other way to accomplish it?

3. ## Re: Negative Time (All)

Yes, that is what I was meant to say.

Essentially what I need it for is time sheets.

Take for example an average working day is 8 hours.
If the employee works 9 and a half hours, it will return (positive) 1 and a half hours.
Alternatively if the employee only works for 7 hours, it SHOULD return a negative 1 hour.
Overall, in the above example there is a total of (negative) half an hour owing.

Is there any way around the above situation?

Rlay

4. ## Re: Negative Time (All)

My office uses a wierd timesheet too.

Hva a look at the attached as it deals with negative time using a separate field that indicates +/-.

I have another version of a timesheet that uses text only, and converts the text to/from time at calculation time. If you want it, I will try to find it too...

5. ## Re: Negative Time (All)

You can have Excel calculate with negative times, but you have to change the date system for that: Tools, Options, Calculation, 1904 date system.

This is a workbook-specific setting.

***WARNING***

When you change this setting, all dates in your workbook will change (the underlying data -the DateSerial number- remains the same, but the displayed date and all calculations based on that date will change). To get the original dates back, subtract the number 1462 from them (or add if you're moving back of course).

6. ## Re: Negative Time (All)

Work-around w/o 1904 time system:

Calculate/display the time deltas and/or running total as decimal number by multiplying the result (or the summands) by 24 _and_ formatting the cell as number (time is stored as a fraction of a day).

Should you compute the wages you will need this value anyway. Case your users have difficulties with decimal hours i.e. 1.33 h ~ 1:20, convert the (key) numbers to an appropriate text string. Assuming your number in C9:

=IF(C9<0,"- ","+ ") &TEXT(ABS(C9/24),"[h]:mm")