0

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?

2

TimeNow() returns the current time in GMT. 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))
  • I thought this was surely the case but I could not get the math to work out in my local time zone. Have you been able to get a working example in a set time zone? (I may just be bad at math). – David Reed Dec 29 '18 at 12:48
  • @DavidReed Changing the 9.5 to your local time (and + to - if you are ahead of GMT) ought to work. – IllusiveBrian Dec 29 '18 at 14:34
-3

Sample Date Formulas https://help.salesforce.com/articleView?id=formula_examples_dates.htm&type=5

Finding the Number of Business Hours Between Two Date/Times The formula for finding business hours between two Date/Time values expands on the formula for finding elapsed business days. It works on the same principle of using a reference Date/Time, in this case 1/8/1900 at 16:00 GMT (9 a.m. PDT), and then finding your Dates’ respective distances from that reference. The formula rounds the value it finds to the nearest hour and assumes an 8–hour, 9 a.m. – 5 p.m. work day.

You can change the eights in the formula to account for a longer or shorter work day. If you live in a different time zone or your work day doesn’t start at 9:00 a.m., change the reference time to the start of your work day in GMT. See A Note About Date/Time and Time Zones for more information.

enter image description here

  • 2
    The OP is asking about Time only, not about Date or DateTime. – David Cheng Dec 29 '18 at 1:37
  • Once he has the business days, getting time from that is very easy. – Sudipta Deb Dec 29 '18 at 4:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.