Don't have an account?

Browse by Topic

+ Start a Discussion

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!

November 8, 2011
·
Like
0
·
Follow
1

Best Answer chosen by Admin (Salesforce Developers)

dizzyem
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")

November 9, 2011
·
Like
2
·
Dislike
0

Steve :-/
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!

November 8, 2011
·
Like
2
·
Dislike
0

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

November 8, 2011
·
Like
0
·
Dislike
0

Steve :-/
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.

November 8, 2011
·
Like
0
·
Dislike
0

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

November 8, 2011
·
Like
0
·
Dislike
0

dizzyem
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))

November 8, 2011
·
Like
0
·
Dislike
0

dizzyem
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")

November 8, 2011
·
Like
0
·
Dislike
0

Steve :-/
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?

November 8, 2011
·
Like
0
·
Dislike
0

dizzyem
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

November 9, 2011
·
Like
0
·
Dislike
0

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

November 9, 2011
·
Like
1
·
Dislike
0

Steve :-/
I think you nailed it (nice work!)

November 9, 2011
·
Like
0
·
Dislike
0

This was selected as the best answer

dizzyem
Thank you for your help!!!

November 9, 2011
·
Like
0
·
Dislike
0

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

November 23, 2011
·
Like
0
·
Dislike
0

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

November 28, 2011
·
Like
0
·
Dislike
0

dizzyem
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!

November 28, 2011
·
Like
0
·
Dislike
0

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

November 28, 2011
·
Like
0
·
Dislike
0

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

November 28, 2011
·
Like
0
·
Dislike
0

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

November 29, 2011
·
Like
0
·
Dislike
0

mmrr
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

December 7, 2011
·
Like
0
·
Dislike
0

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

December 7, 2011
·
Like
0
·
Dislike
0

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

December 7, 2011
·
Like
0
·
Dislike
0

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

December 7, 2011
·
Like
0
·
Dislike
0

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

December 7, 2011
·
Like
0
·
Dislike
0

dizzyem
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

December 7, 2011
·
Like
0
·
Dislike
0

mmrr
Thank you so much dizzyem!

I created a trigger - and it worked!

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

December 7, 2011
·
Like
0
·
Dislike
0

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

December 8, 2011
·
Like
0
·
Dislike
0

Josephine 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")

November 5, 2015
·
Like
1
·
Dislike
0

Janno Rip
Hello there,

I got the same requirement and I'm running into "The formula expression is invalid: Syntax error" when using this formula in my flow.

DATETIMEVALUE( TEXT(YEAR({!varNextDay})) +"-"+ TEXT(MONTH({!varNextDay})) +"-"+ TEXT(DAY({!varNextDay})) +" "+ TEXT(VALUE(LEFT({!getContact.Uhrzeit_Erreichbarkeit_als_Text__c}, 2))+ 7)+ MID({!getContact.Uhrzeit_Erreichbarkeit_als_Text__c},3,3)+":00" )

{!varNextDay} is a date field I calculate in my flow
{!getContact.Uhrzeit_Erreichbarkeit_als_Text__c} is a formula text field which contents look like this: 10:00 AM

Where did I go wrong?

July 27, 2020
·
Like
1
·
Dislike
0

Karim Ullah
Convert string offset to Time zone in formula fields
In this formula field I have string State_province_Timezone__c ='+07:00' which is country offset and I want to convert current time to that offset timezone. In first Line (5/24) is current org timezone offset. So I extract hours and minute and calculate time zone the return is datetime.You can also use value instead of timevalue().
(NOW()- (5/24) )+
( IF(LEFT(State_province_Timezone__c,1) = "+",1 ,-1) *
((VALUE(MID(State_province_Timezone__c, 2, 2)) * 60) + VALUE(RIGHT(State_province_Timezone__c, 2))) / 1440)

February 2, 2023
·
Like
0
·
Dislike
0

You need to sign in to do that.

Need an account? Sign Up

Have an account? Sign In

Dismiss

Browse by Topic

Welcome to Support!

Show

sorted by

Formula to create date/time value

All Answers

DATETIMEVALUE

You need to sign in to do that.

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!

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.