Forum Discussion
How can I display negative values for time in calculation results
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