TIMENOW() subtraction
I am trying to find out the difference between the current time and 5PM. I tried numerous variations and I just can't make sense of the results I am getting. Lets say current time is 15:36 (3:34 PM) When I run this formula
(TIMENOW() - TIMEVALUE("00:17:00.000"))
I get the following:
83949787
Given the fact that time time is 15:36 and I want to discover how long till 17:00 (5PM) I would expect one of these:
1h 24m
or
104 (minutes)
or
6,240 (seconds)
or
6,240,000 (milliseconds)
I can't make sense of this. Is this a fraction of a day, milliseconds....a decimal value?
The TIMENOW() function returns a value in GMT representing the current time without the date. You can use this function instead of the NOW() function if you want the current hour, minute, seconds, or milliseconds. When the user views a Time field it's converted to their local timezone, but the value in the field is also always stored as GMT. The almost-84 million number you're getting is the result of subtracting a later timevalue from an earlier one - rather than give a negative number, the system appears to calculate it as if the second number was actually that time the previous day. There are 84,400,000 milliseconds in a day, so your number would indicate the actual value from TimeNow() is just after midnight. You said you were trying at 3:34 PM Local time, so I'm guessing your offset is set to UTC -9:30 (French Polynesia) which would give a GMT time of 00:04, just before your subtracted value of 00:17. Since you said you wanted to test since 5 PM local time, you'll need to account for your time offset, so your formula should be the following to get the time in milliseconds:
TIMENOW() - (TIMEVALUE('17:00:00.000') + (9.5 * 60 * 60 * 1000))This will help you find out differences between the current time and specific time.
Answer (1)
One thing to keep in mind is that TIMENOW() returns a time value in milliseconds since midnight GMT, not a formatted time string. That's why you're seeing a large number instead of hours or minutes.
If your goal is to find the time remaining until 5 PM, you need to subtract the current time from the target time, not the other way around. Also make sure you're accounting for your timezone since TIMENOW() uses GMT. Once you have the difference in milliseconds, you can convert it to minutes or hours by dividing by 60,000 or 3,600,000 respectively.
The timezone adjustment mentioned in the accepted heardle online answer is probably the key reason your results don't match what you expect.