function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Amanda Byrne- Carolina Tiger RescueAmanda Byrne- Carolina Tiger Rescue 

Populating a DateTime field from a date picker and time dropdown in Visual Flow

I have a flow that collects the date from one field, the time from a dropdown, and then uses a formula to combine the two to use to create an Event Record.

User-added image

I've used various combinations trying to get the formula to create something that can be used to populate the StartDateTime and the ActivityDateTime fields, but can't find a way to get it to work.

I have my choice stored values for time set to return miltary hours:minutes:seconds, e.g. 13:00:00

My current formula is: Text({!Tour_Date}) + " " + {!Tour_Time}

As a text formula this shows on my display text screen (that I'm using to verify my field inputs prior to using Record Create) as 2014-11-24 1:00PM, which seems to be as close as I can get as an input for the Event DateTime format.

But as soon as I change the formula value data type to DateTime, or if I add DATETIMEVALUE() around the formula above, my test sceen in the flow shows nothing for the result.

What am I missing?
Best Answer chosen by Amanda Byrne- Carolina Tiger Rescue
Amanda Byrne- Carolina Tiger RescueAmanda Byrne- Carolina Tiger Rescue
Ah, well there's a bug- or at least something I didn't expect.  I swear I had tried that formula already, but I tweaked something when I tested the formula again.

I changed my Tour Time to 'Twilight Tour'

My dropdown values in the flow have a label, a unique name, and a stored value. My labels were 10:00 AM, 11:00 AM, 1:00 PM, 2:00 PM, and Twilight Tour and my stored values were 10:00:00, 11:00:00, 13:00:00, 14:00:00, and 18:00:00.

I thought the stored value was what was actually the STORED VALUE for that screen input.  Silly me.  When I ran the test, this time the Tour Time was listed as 'Twilight Tour'.  I hadn't questioned it before because I just assumed that 10:00 AM was being converted back to a time format before it was displayed on my test screen.

I changed the labels to 10:00, 11:00, 13:00, 14:00, and 18:00, and changed my formula to:
DATETIMEVALUE(TEXT({!Tour_Date})&" "&{!Tour_Time}&":00")

I would prefer that my users didn't have to deal with military time format, but screw it, they can figure it out.

The record create works now too.  Hurray!

 

All Answers

claperclaper
In your fomula try using 
DateTime.newInstance(Year, Month, Day, Hour, Minutes, Seconds);
claperclaper

If that doesnt solve it try: 
DATETIMEVALUE( "2013-08-01 12:00:00" )
 

Amanda Byrne- Carolina Tiger RescueAmanda Byrne- Carolina Tiger Rescue
DateTime.newInstance throwns an unknown function error.  I've already tried the second one, that doesn't work either.
claperclaper
How about this one : DATETIMEVALUE(TEXT({!TestDate})&" "&{!TestTime})

I just re-created the scenario in my dev org: here is a screencast of it working: 
http://screencast-o-matic.com/watch/c2XZrGeYyY
Amanda Byrne- Carolina Tiger RescueAmanda Byrne- Carolina Tiger Rescue
Ah, well there's a bug- or at least something I didn't expect.  I swear I had tried that formula already, but I tweaked something when I tested the formula again.

I changed my Tour Time to 'Twilight Tour'

My dropdown values in the flow have a label, a unique name, and a stored value. My labels were 10:00 AM, 11:00 AM, 1:00 PM, 2:00 PM, and Twilight Tour and my stored values were 10:00:00, 11:00:00, 13:00:00, 14:00:00, and 18:00:00.

I thought the stored value was what was actually the STORED VALUE for that screen input.  Silly me.  When I ran the test, this time the Tour Time was listed as 'Twilight Tour'.  I hadn't questioned it before because I just assumed that 10:00 AM was being converted back to a time format before it was displayed on my test screen.

I changed the labels to 10:00, 11:00, 13:00, 14:00, and 18:00, and changed my formula to:
DATETIMEVALUE(TEXT({!Tour_Date})&" "&{!Tour_Time}&":00")

I would prefer that my users didn't have to deal with military time format, but screw it, they can figure it out.

The record create works now too.  Hurray!

 
This was selected as the best answer
claperclaper
Awesome!!! Glad it's working now!
claperclaper
Hi Carolina, would you mind marking this question as solved so it can help out others when looking for answers?

Thanks!
Jeff BergerJeff Berger
Carolina, did you ever figure out how to move away from military time and use the stored values instead of the labels? I am in a similar position as you and would prefer to avoid the military time if I can.
Thanks!
Scott M - SFDC FanScott M - SFDC Fan
I've wrote up a blog that might help a bit to do datetime right in visual workflow. 

http://salesforce-tips.blogspot.de/2016/01/salesforce-visual-workflow-with.html

Hope it helps.

Scott
Susan Ross-WentworthSusan Ross-Wentworth
Hi,

I have a question for Scott M - SFDC Fan, who posted that great solution about using the offest container and apex (http://salesforce-tips.blogspot.de/2016/01/salesforce-visual-workflow-with.html).  I tried his solution and it worked great in most cases but not all.

It works great for any time values from 10:00 am onward, but will not work for times prior to 10 am.  For example, an input value of 8:30 am results in a calculated date time value which has a three-day offset from my entered date value and a three-hour offset from my entered time (it becomes 11:30 am).  A 9:00 am input value sometimes comes back as 9:00 pm.

I’ve tried various way to get around this but nothing has worked:

The blog mentions using text values of 01:00, 01:15, etc. for the time input values.  I used that format, starting with 08:30, 09:00, 09:30, etc.  When I tried to save the flow, I got an error saying the “SelectionAsNumber” formulas were invalid because of an “incorrect parameter for function “TEXT()”.  Expected Number, Date, DateTime, Picklist, received Text.”

So I changed my time input values to be numbers, starting with 0830, 0900, 0930, 1000, etc.  I used the same “SelectionAsNumber” formulas as before.  Everything then worked beautifully for all time values of 10 am (1000) onward, but not for the times between 8:30 am and 9:30 am (the offset is calculated incorrectly and is off by several days and several hours).

I tried going back to using text for my time input values and took the TEXT() reference out of the “SelectionAsNumber” formulas, leaving everything else the same.  That produces no converted DateTime value at all.

Since everything after 10 am is working, I wish I could just drop the earlier time values, but I have to be able to include them in my input values.

Thanks!!
Tracy Oden 31Tracy Oden 31
I would like help on how to do this. I have the same requirement. Can someone offer a solution that ensures accurate time with timezone offset.