jpcc1
Blogger
Share this content
0
5
2423

Negative time

Negative time

How can I format a cell so that it displays a negative time rather than ####  ??

e,g, for use in flexi- time calculations.

Replies

Please login or register to join the discussion.

By shurst
14th Feb 2011 10:33

Negative time

There are a few ways to achieve this, but if you just wanted the result as a number of hours or even minutes, the simplest way would be to format the result as a number rather than a date/time value. This would be expressed in terms of days, but you could multiply by 24 to get hours and decimal fractions of hours and by another 60 if you wanted it in minutes. John Walkenbach suggests the use of the 1904 date system with provisos here: http://j-walk.com/ss/excel/usertips/tip051.htm

Another idea would be to use the ABS() function to convert the negative values to positives and then use conditional formatting based on the original calculation to highlight the negative values - you would have to make sure you didn't include the ABS() formula in future calculations that mixed positives and negatives of course.

Hope it helps

Simon

Thanks (0)
17th Feb 2011 10:00

Funny you should ask

We just published an Excel FAQ article on hours and minutes last week.

I didn't include anything on negative times in the text, but Bill Jelen shows how to locate and use the 1904 dating system in the MrExcel video at the end.

Thanks to Simon for popping in to provide further advice.

Thanks (0)
avatar
17th Feb 2011 13:47

Personally I use 2 columns/rows

On my timesheets I have a "Flexi Accrued" Column and a "Flexi Used" column. These are calculated using the "IF" formula so that time is always positive, but appears in different columns based on whether accrued or used.

Far Easier than ABS() as with ABS() you would need to manually adjust the formula at the end, or get really complicated, in order to ensure all flexi is correctly accounted for.

Thanks (0)
By shurst
17th Feb 2011 14:12

MAX() and MIN() v IF()

If you do go the two column route then using the MAX() and MIN() functions gives a slightly shorter formula than IF().

E.g. =MAX(0,A2) for the positive columns and =MIN(0,A2) for negatives.

Thanks (0)
avatar
17th Feb 2011 14:35

Negative times

If you are only interested in the presentation, then the following may work for you:

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

This retuurns a text string that should not be used in any further calculations.

Regards,

Jason Raikes

www.pendragonsystems.com

Thanks (0)