Print this page

Formula for creating Date/Time field using Date field and Time (Time as Picklist)

Knowledge Article Number 000187982
Description A Field to hold DateTime value can be created from a field holding Date value and a picklist field holding Time values. There are some consideration and limitation for this formula. 

  1. Consider the time populates as Picklist not Textbox. Refer the attached file "Time (Picklist Sample Values).txt".
  2. Assume that below 2 fields are the INPUT for the requirement:
    Field NameField TypeValues (example)
    Start DATEDate23/12/2013
    Start TIMEPicklist12:55 PM
  3. Consider Timezone for the user (under Name-> My Settings -> Language & Time Zone) as “(GMT-05:00) EST (America/New_York)”.
  4. For other Timezones, change the occurrence of “5” in attached formulas as per below table:
    Time ZoneReplace +5 with (from below formulas)


First version of this article will only work for Timezone with “GMT +/- XX:00” format i.e. this will not work for 12 Timezones like “GMT+05:30”, "GMT + 13:45", “GMT-09:30” but will work for rest all like "GMT + 14:00", "GMT + 15:00".


This would be the OUTPUT for this requirement:

 Field NameField TypeValues (example)
Start DATE/TIMEDate/Time23/12/2013 12:55 PM
Resolution Create:
Field NameField TypeValues (example)Notes
Start DateTime (Temp)Date/Time23/12/2013 12:55 PMHidden from page layout
Start DATE/TIMEFormula (Date/Time)23/12/2013 12:55 PMDisplay this on page layout
For Start DATE/TIME formula field Refer Formula 2 from the attached file “Date-Time (Formula 1 & 2).txt”.

Now create below workflow:
Rule NameWF Update Start Date/Time (Temp)
ObjectSelect required object
Rule CriteriaOR( ISCHANGED( Start_DATE__c ) , ISCHANGED( Start_TIME__c ))

Field Update
NameUpdate Field Start Date/Time (Temp)
ObjectSelect previously selected object where intermediate field was create
Field to UpdateStart DATE/TIME (Temp)
Formula ValueRefer Formula 1 from attached formula “Date-Time (Formula 1 & 2).txt”
  1. First, the above workflow fires whenever either Start DATE or Start TIME is changed.
  2. This workflow will update a temp field Start DATE/TIME (Temp).
  3. Finally, a formula field uses this temp field and adds some more calculations to this and returns the required date/time value.

promote demote