How can I format a cell so that it displays a negative time rather than #### ??
e,g, for use in flexi- time calculations.
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
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.
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.
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.
If you are only interested in the presentation, then the following may work for you:
This retuurns a text string that should not be used in any further calculations.