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?
4 Replies
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.
