+ Start a Discussion
dizzyemdizzyem 

Formula to create date/time value

How would I write a formula to take a regular date field (11/15/2011) and a text field that represents a time in this format (08:30 AM) into a date/time formula field? Any help is appreciated. Thanks!

 

 

Best Answer chosen by Admin (Salesforce Developers) 
dizzyemdizzyem

Here is the final formula:

 

DATETIMEVALUE(TEXT(YEAR(Start_Date__c))+"-" +TEXT(MONTH(Start_Date__c))+"-" +TEXT(DAY(Start_Date__c))+" "+TEXT(VALUE(LEFT(Start_Time__c, 2))+ 7)+  MID(Start_Time__c,3,3)+":00")

All Answers

Steve MolisSteve Molis

You would need to use a DATETIMEVALUE function for that.

 

DATETIMEVALUE

Description:Returns a year, month, day and GMT time value.
Use:DATETIMEVALUE(expression)and replace expression with a date/time or text value, merge field, or expression.
Example:Closed Date

DATETIMEVALUE(ClosedDate)displays a date field based on the value of the Date/Time Closed field.

Literal Date Value

DATETIMEVALUE("2005-11-15 17:00:00")returns November 15, 2005 5:00 PM GMT as a date and time value .

Tips:
  • DATETIMEVALUE is always calculated using GMT time zone and can't be changed.
  • When entering a date as a literal value, surround the date with quotes and use the following format: YYYY-MM-DD, that is, a four-digit year, two-digit month, and two-digit day.
  • If the expression does not match valid date ranges, such as the MM is not between 01 and 12, the formula field displays #ERROR!
dizzyemdizzyem

I thought that might be the way but can't figure out exactly how to pass the values from those two fields in to the expression... if I do somthing like this:

 

DATETIMEVALUE((Start_Date__c) +VALUE(Start_Time__c))

 

I get an error. I'm not sure if I should be trying to format the Start Time, the Start Date, or both to get it into the proper format for the DATETIMEVALUE function since the start date is formatted as 11/15/2011 right now and the time is formatted at 08:30 AM.

Steve MolisSteve Molis

You need to enter the values in the EXACT format as the example posted in the Formula Guide.  Also, remember that the Time component MUST be in GMT format.

dizzyemdizzyem

I don't know how to convert those values into the proper format.

dizzyemdizzyem

Okay, so if I format the date field I can get this far... but I'm still not sure how to format the text field that is holding the time value...

 

DATETIMEVALUE(TEXT(YEAR(Start_Date__c))+"-"+TEXT(MONTH(Start_Date__c))+"-"+TEXT(DAY(Start_Date__c))+" "+(Start_Time__c))

dizzyemdizzyem

Now I'm here... but still getting #ERROR!

 

DATETIMEVALUE(TEXT(YEAR(Start_Date__c))+"-"+TEXT(MONTH(Start_Date__c))+"-"+TEXT(DAY(Start_Date__c))+" "+ LEFT(Start_Time__c, 5)+":00")

Steve MolisSteve Molis

I've gone over your code and nothing is jumping out at me as the error.  When in doubt I create a Formula(Text) field an d write out my result to that and compare it to the required DATETIMEVALUE format and look for discrepancies.  

 

Can you give that a shot?

dizzyemdizzyem

Okay, I create two test fields (both formula - text) and compared the three values. For some reason the value in my Start_Date_Time__c field for time is still incorrect, but I am getting close. When I put the formula in to a text field it shows the correct time 08:00:00 but in my start_date_time__c field it is showing as 1:00 AM. Any ideas when I would change in the code to correct this?

 

Time Frame Name             Start Date Time                 Test                                   Test 2

 

Main Schedule                   11/15/2011 1:00 AM          2011-11-15 08:00:00        08:00:00

dizzyemdizzyem

Nevermind... we are GMT - 7 here so I am assuming I will need to add 7 to the start time. 

Steve MolisSteve Molis

I think you nailed it (nice work!)

dizzyemdizzyem

Here is the final formula:

 

DATETIMEVALUE(TEXT(YEAR(Start_Date__c))+"-" +TEXT(MONTH(Start_Date__c))+"-" +TEXT(DAY(Start_Date__c))+" "+TEXT(VALUE(LEFT(Start_Time__c, 2))+ 7)+  MID(Start_Time__c,3,3)+":00")

This was selected as the best answer
dizzyemdizzyem

Thank you for your help!!!

MichaelWouldMichaelWould

Hi,

 

Great formula, thank you for posting and I'll apologise in advance for bothering you, but am a newbie...

 

We are -11 hours GMT, when selecting a time such as 9:30 AM I get an #error,  but when selecting a time that allows for -11 such as 11:30 AM it works....how do I edit the formula to cater for earlier time slots...

 

Thanks in advance.

 

 

dizzyemdizzyem

That is a good point, I had not thought about that situation.

 

One way would be to create two date/time formula fields... one using the formula I posted earlier (but removing the +7 that I added to the hours). Then creating a second date/time formula field that takes the entire date/time value and adds or subtracts the number of minutes between your time zone and GMT (as detailed in salesforce help at this URL: https://cs9.salesforce.com/help/doc/user_ed.jsp)

 

So, when I did this my first date/time formula field was:

 

DATETIMEVALUE(TEXT(YEAR(Start_Date__c))+"-" 
+TEXT(MONTH(Start_Date__c))+"-" 
+TEXT(DAY(Start_Date__c))+" "+TEXT(VALUE(LEFT(Start_Time__c, 2)))+ MID(Start_Time__c,3,3)+":00")

 

and my second date/time formula field was:

 

Start_Date_Time__c + 0.2917

 

I had to add 7 hours instead of subtracting 7 hours like they did in the salesforce example. You will have to do your calculation for -11 hours and try that... if it seems backwards add it instead and see if that's what you need.

 

I'm sure there is probably a way to do this all within one formula field but I was running into errors trying to add the +0.2917 onto the first formula.

 





dizzyemdizzyem

LOL I must be completely brain dead today from the holidays. Here it is in one formula:

 

(DATETIMEVALUE(TEXT(YEAR(Start_Date__c))+"-" 
+TEXT(MONTH(Start_Date__c))+"-" 
+TEXT(DAY(Start_Date__c))+" "+TEXT(VALUE(LEFT(Start_Time__c, 2)))+ MID(Start_Time__c,3,3)+":00"))+ 0.2917

 

the +0.2917 on the end is what I had to add to compensate for the difference between my time zone and GMT. Replace with your calculation for the difference between your time zone and GMT.

 

Thanks!

dizzyemdizzyem

I added logic to make sure that the AM and PM signs stay intact:

 

IF(RIGHT(Start_Time__c, 2) = "AM", 
(DATETIMEVALUE(TEXT(YEAR(Start_Date__c))+"-" 
+TEXT(MONTH(Start_Date__c))+"-" 
+TEXT(DAY(Start_Date__c))+" "+TEXT(VALUE(LEFT(Start_Time__c, 2)))+ MID(Start_Time__c,3,3)+":00"))+ 0.2917, 
(DATETIMEVALUE(TEXT(YEAR(Start_Date__c))+"-" 
+TEXT(MONTH(Start_Date__c))+"-" 
+TEXT(DAY(Start_Date__c))+" "+TEXT(VALUE(LEFT(Start_Time__c, 2)))+ MID(Start_Time__c,3,3)+":00"))+ 0.7917)

 

If the start time ends with AM then I am only adding the 7 hours for the difference between my timezone and GMT. Else (the start time ends with PM) I am adding the 7 hours plus 12 more hours (0.7917) to get the date/time value to show as PM.

MichaelWouldMichaelWould

BRILLIANT!

 

Thank you sooo much.  

 

FYI the formula with the logic for making the AM/PM stay in tact was too large (over 5,000 characters), but the values we will be picking from it won't impact this.

dizzyemdizzyem

Weird... It only compiled to 2,413 for me. But glad the other stuff helped you!

mmrrmmrr

While trying this formula, I am getting syntax error - (​MONTH(Start_Date__c))+"- " - please help me,  also please explain me this part of the code - (Start_Time__c,3,3)+":00. Thanks in advance

dizzyemdizzyem

Can you post your entire formula so that I can review it in context?

 

The MID(Start_Time__c,3,3)+":00" section of the formula is taking the middle portion of my start time (08:30 AM) starting with the third character and taking the next three characters. So the result of that section leaves me with :30 and then I'm adding :00 to that. So, in the end I have the my full time string... something like 08:30:00. 

 

Here is the help description of how MID works...

 

MID

Description:Returns the specified number of characters from the middle of a text string given the starting position.
Use:MID(text, start_num, num_chars) and replace text with the field or expression to use when returning characters; replacestart_num with the number of characters from the left to use as a starting position; replace num_chars with the total number of characters to return.
Example:MID(Division, 3, 4) returns four characters of the Divisionname beginning with the third character from the left. On a user record, this represents the department code.
mmrrmmrr

Thank for the great explanation!

The formula I gave is :

 

DATETIMEVALUE(TEXT(YEAR(Start_Date__c))+"-" +TEXT(​MONTH(Start_Date__c))+"-" +TEXT(DAY(Start_Date__c)​)+" "+TEXT(VALUE(LEFT(Start_Time__c, 2))+ 7)+  MID(Start_Time__c,3,3)+":00")

 

My requirement is to throw error  message for events scheduled before 8.00 am.

dizzyemdizzyem

I think it has something to do with the way the quotations copied and pasted. I tried in my environment and got the same error but compared character to character with mine and didn't see any issues. So I typed it in rather than copying and it worked just fine. Try typing the formula in instead of copying and pasting and see if that works for you.

mmrrmmrr

Thanks again!  It doesn't show syntax error now.

The field name in the event is StartDateTime, so I replaced this wherever Start_Date__c/Start_Time_c.  Now it says that the field does not exist - this is a standard field in event.  Am I missing something or does this mean that this formula should not be used in this context? Please advise.

dizzyemdizzyem

Since the StartDateTime field on the Event object is already a Date/Time field you don't actually need this formula (this formula takes two fields and puts them in to the date/time format, but the StartDateTime field is already in the date/time format. If you are trying to throw an error prior to a certain time, you will need to parse the time out of the StartDateTime field...

 

MID (TEXT (Start_Date_Time__c - 0.2917), 12, 5)

 

That will give you just the hour. Then you can create your if clause so that if the result is earlier than the time specified you can give the user an error.

 

Here is a good link to help with that:

 

http://success.salesforce.com/questionDetail?qid=a1X30000000IzRtEAK

mmrrmmrr

Thank you so much dizzyem!

 

I created a trigger - and it worked!

 

for(event e :Trigger.New){
    if(e.startdatetime.hour() < 8 {}

 

 

dizzyemdizzyem

Even better! Glad you were able to work it out!

Josephine Labat 3Josephine Labat 3
Just a quick with a workaround I found with Daylight Saving time. It still requires manual entry but at least work for a while when no development works:

First create a Field that returns -1 or 0 if a date is in Summer Time:
IF ( AND (Start_Date__c > DATE(2015,3,29),Start_Date__c < DATE(2015,10,25)),-1, 
IF ( AND (Start_Date__c > DATE(2016,3,27),Start_Date__c < DATE(2016,10,30)),-1, 
IF ( AND (Start_Date__c > DATE(2017,3,26),Start_Date__c < DATE(2017,10,29)),-1, 
IF ( AND (Start_Date__c > DATE(2018,3,25),Start_Date__c < DATE(2018,10,28)),-1, 
IF ( AND (Start_Date__c > DATE(2019,3,31),Start_Date__c < DATE(2019,10,27)),-1, 
0)))))

Then add this field into the original Date/Time formula:
DATETIMEVALUE(TEXT(YEAR(Start_Date__c)) 
&"-" 
&TEXT(MONTH(Start_Date__c)) 
&"-" 
&TEXT(DAY(Start_Date__c)) 
&" " 
&TEXT(VALUE(LEFT( Start_Time__c,2)) + GMT_vs_BST__c ) 
&":" 
&TEXT(VALUE(RIGHT( Start_Time__c,2))) 
&":00")