0

Is there a way break out the seconds and milliseconds from a date time field in a formula field?

WHY: I want our crew people to use check-in and check-out from their mobile device. However, if they forget to check in or don't have service, I need to give them the ability to manually override the time. I want the supervisor to be able to identify if the device did it vs end user. I can create a formula field that looks at milliseconds and if they are 0 then I would assume the date was modified manually.

  • 1
    You can via Apex... – Adrian Larson Jul 22 '16 at 17:12
  • 1
    After looking at it deeper, I'm not 100% sure the system actually stores millisecond values... – Adrian Larson Jul 22 '16 at 17:37
  • I was able to parse it out when creating the date by using string.valueOf(checkintime.getTime/1000).left(3) however, the UI didn't have the precision to chop it off on edit, so it just left it. – tjcinnamon Jul 22 '16 at 17:42
1

The first part of the Griffin's answer is incorrect (should be RIGHT instead of LEFT).

This formula will give you the seconds as a number. As Griffin's answer states, you must use apex if you need milliseconds.

VALUE(
    LEFT(
        RIGHT(
            TEXT(now())
        ,3)
    ,2)
)
  • My formula was not incorrect. The use case was checking for 0 seconds. Your formula does not actually meet the use case it simply returns the number of seconds it does not incorporate the true/false statement. – Griffin Mar 7 '17 at 20:28
  • @Griffin actually your formula is incorrect. LEFT(3) returns the first 3 characters for the year in the date part (201), not the time – NSjonas Mar 8 '17 at 7:05
  • You are correct and I retract my statement. I'll edit my answer. – Griffin Mar 8 '17 at 16:57
1

While NSjonas's answer does get you the correct result it does not follow Salesforce best practices and is thus incorrect.

Users should minimize the number of functions being used when possible. To do this the proper formula should be:

VALUE(
    MID(
        TEXT(NOW())
    ,18,2)
)

To solve the use case or for how to work with milliseconds please refer to my original answer.

1

No. The closest you can get is in seconds and even that involves some gymnastics. In order to check the seconds on a DateTime field you can do the following:

IF((RIGHT(TEXT(TODAY()),3) == '00Z'),true,false);

This would return true of the seconds are 0 and false if the seconds are anything but. I don't recommend this seeing as you really have a 1/60 change instead of the 1/6000 you would if milliseconds were included.

Now you could use Apex. There it's pretty simple.

Datetime yourDate = Datetime.time();
Integer seconds = yourDate.second();
Integer milliseconds = yourDate.millisecond();

Boolean manualUpdate = false;

if(seconds == 0 && milliseconds == 0){
        manualUpdate = true;
}

Further DateTime function can be found in documentation.

  • I was trying this: system.debug(string.valueOf(a.check_in_time__c.getTime()/1000).left(3)); However, what is your milliseconds variable? – tjcinnamon Jul 22 '16 at 16:45
  • @tjcinnamon Oops! My bad. I updated it so it should work. I'm using built in Datetime functions – Griffin Jul 22 '16 at 16:54
  • When I use millisecond() it always returns 0 – tjcinnamon Jul 22 '16 at 17:33
  • When I use (string.valueOf(a.check_in_time__c.getTime()/1000).left(3), the milliseconds don't change when I manually edit the time from the UI – tjcinnamon Jul 22 '16 at 17:34
  • 1
    @tjcinnamon I don't think Salesforce has the whole DateTime thing down right now. It's doing some Javascript kinda stuff (Context: goo.gl/wyk4f1) – Griffin Jul 22 '16 at 17:44

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.