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 ...
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)