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. 

Consideration:
  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)
    GMT-06:00+6
    GMT+08:00-8

Limitation:

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".


Requirement:

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”
 
Explanation:
  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