Forum Discussion
How can I display negative values for time in calculation results
I'm trying to create an Excel spreadsheet with a column that shows the time relative to a predetermined point in time in 15-minute intervals with negative values for time in the cells above 0:00 and positive values below. I have the correct formulas entered in, but I can't seem to figure out how to get Excel to display negative values for time. Is there a way to do this?
7 Replies
Thanks for all the suggestions. I've made do without, but it was really aggravating me at the time. What I was looking for was a way to enter a formula that subtracts "=NOW()" from each time entered in one column and returns a positive value if that time is in the future, and a negative value if it's in the future. For example:
If cell A1=14:00, cell B1="=A1-NOW()", and "=NOW()"= 12:30, then it's 14:00 - 12:30 = 1:30
Similarly,
If cell A2 = 11:15, cell B2="=A1-NOW()", and "=NOW()"= 12:30, then it's 11:15 - 14:00 = (-1:15)
So each time the page recalculates, the values shown in Column B will change.
As a comment, spreadsheets (not only Excel, - Google Sheets, Rows, etc) typically don't work with durations, only with time. And, as was mentioned, there is no such thing in this world as negative time. Thus workarounds - show "time" as decimal number, as text, etc.
One more. NOW() returns datetime, not time. Like
To get time differences to display as hours and minutes (as opposed to a decimal part of a day), the value must be recorded as positive, even it that means using a formula in the form
= ABS(end - start)That doesn't prevent you from formatting the number to show as negative (with a minus sign or even with the accountancy trick of a red value within parentheses). A formula-based conditional format using the condition
= start > endcould be used to apply the number format
[Red]([h]:mm)The catch is that, like the text solution, the value in the cell cannot be used directly in dependent formula without correcting the sign.
There is another different trick used to express fractions by showing the numerator after the decimal place, with the denominator as given within the function. So
= DOLLARFR(24*(end - start), 60) will give -1.45for a negative time difference of 1 hour 45 minutes.
As mathetes​ mentioned, there's no such thing as negative time. BUT..... switching Excel to use the 1904 date system will allow you to calculate with and display negative time values.
Though, changing the data system will shift all dates in your file by 4 years. If you don't have real dates in this particular workbooks that may not be a problem then.
If you just want to show negative sign before time value then you could try TEXT() function like-
=TEXT(A1-TIME(0,15,0),"-hh:mm AM/PM")To make it conditional you can use IF() or IFS() function.
To build on Harun's answer for what I think is you specific case:
=LET(timediff, $B$1 - A1:A100, IF(timediff<0,"-","")& TEXT(ABS(timediff),"[h]:mm:ss.0"))where B1 is you fixed time value and A1:A100 is your range of times
this will unfortunately produce TEXT values (i.e. you will NOT be able to math with them)
Wouldn't the only negative values in time be those before the Big Bang?
You don't display what you're talking about, but picking any old arbitrary time as the "predetermined point," say 4:57, the time displaying on my screen as I type this, wouldn't 15 minutes ago be 4:42, before that 4:27, and so forth. I.e., they're not "negative time values," they're just earlier time values.
