2

I have a flow containing a date/time selection screen comprised of a date picker and a dropdown field with different time values. Since I'm creating an Event I then have a formula field which puts the date and time together to created the ActivityDate value which I then insert.

My issue is that I'm being forced into using 24:00 time (military time) in my choice labels because the formula seems to be calculating on the label value instead of the stored value for each choice.

Can I force the formula to reference the stored value instead of the choice so my users don't have to deal with military time when trying to create their event?

Here is the formula I'm using to get my DateTime value for ActivityDate:

DATETIMEVALUE(TEXT({!Date})&" "&{!Time}&":00")+0.2083333333

(the + value is to get the time from UTC over to CT where my business is located)

Thanks!

  • 1
    you sure the difference between UTC and US Eastern Time will always be constant? – Charles Koppelman Jul 27 '15 at 16:57
1

You can use formula fields to create a Date time from any kind of format, including AM/PM format, not just 24 hour military time. See Useful Formula Fields for more on how to do this. You can add an If Statment to your formula to detect the presence of a PM that will add 12 hours to the time before parsing it as a DATETIMEVALUE.

You can also use parse(datetime string) to convert a date + AM/PM time string in the local user's Time Zone to a DateTime using Apex instead of formulas and offsets.

Also be aware that Salesforce will convert all datetime records to GMT before storing them in the database, then will display them in the User's time zone when they're retrieved. The fact that Daylight Savings Time becomes involved when calculating any time offsets is discussed in the above document. It recommends using Apex for any conversions because of the varying nature of when they come in and out and change the effect on the offset.

| improve this answer | |
  • Even in your flow, any DateTime values created will still be created in the context of the user and then stored in GMT. Because of that, I would not be concerned with the time zone offset. Try it and I think you'll see it's not necessary. And BTW, you can create an APEX plug-in to do this for you should you find you need one. – crmprogdev Jul 27 '15 at 19:00
  • I wish I could use Apex because it would make this whole thing easier. Alas I'm stuck with declarative tools for now. I can see how they are transforming the DateTime to a string in "HH:MM:SS A/PM" format but I'm not sure I can tell what I need to do to my {!Time} value in order to account for the AM/PM. Do I need to convert my {!Time} value to text to search for the PM and then convert it back to a TIME value to do the addition of the 12 hours? I don't meant to ask you to write my formula for me but after reviewing those pages I still don't think I have it. Thanks for your help! – Jeff Berger Jul 27 '15 at 19:10
  • What I would do is use a time picker that's in hh:mm AM/PM (:ss isn't needed for a StartTimeDate) as a text string. You can then strip any excess white space, check for the AM or PM at the end & add it to your date. I did something similar on another project once, but that was using APEX. – crmprogdev Jul 27 '15 at 19:33
  • OK so if I modify my existing formula it would be something like this: DATETIMEVALUE(TEXT({!Date})&" "&IF(CONTAINS(TEXT({!Time}),'PM'), {!Time} + 12:00, {!Time})&":00")+0.2083333333 I can't seem to get the flow designer to accept that formula but I'll keep working it. – Jeff Berger Jul 27 '15 at 20:38
  • Create a separate formula first on Time that adds the 12 hours to it if there's a PM and strips out the AM if one exists and returns the results so it's ready to assemble into your date-time. Leaving the AM and PM in there would seem to be problematic which could be the problem. I also see where you're adding seconds to {!Time} in the false condition but not in the true condition? – crmprogdev Jul 28 '15 at 2:20

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.