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.