You need to sign in to do that
Don't have an account?
Create datetime field with custom hours
Hi,
I create a datetime fields and I want to write it the date of CreatedDate with custom hours and minutes. If it will exist a function datetime like date(year,month,day) I solve my problem!!
The problem is that I want to send an email to lead at X hours Y minutes with workflow.
If the lead was created in the night, I don't want send email to lead immediately and so I will send the email in the office time (9am to 8 pm) and not in the night
Maybe I have to do a case condition, because if the lead is created at 9 pm, the datetime field will have the next day at 9 am
I think to write some code like this
date( year(datevalue(createddate)), month(datevalue(createddate)),day(datevalue(createddate)) ) & 09:00:00.000Z
Anyone can help me?
Ah, OK. Just need this then:
You can add time to a date time as fractions of a day. So if you want to add a certain number of hours and minutes your formula would add a certain number of 1/1440's of a day.
e.g.
CreatedDate + 30/1440
Would be the formula for a custom formula field of datetime format to have a value 30 mins after the creation date. If you wanted to base this on other fields on the object you could then use:
CreatedDate + ((MyHours__c * 60) + MyMins__c)/1440
To add on your X hours and Y minutes. HTH
All Answers
maybe I explain my problem in wrong way.
I have to create a custom field called "XX" (type of field datetime) which is :
year, month and day of createddate + specified hours and minutes
after the formula create, I will create a time based workflows which after 3 day after XX do some actions (email,update field, new task etc...)
With a trigger in apex code I will do it very easy, but I want to solve the problem with an easy formula's field...
Ah, OK. Just need this then:
You can add time to a date time as fractions of a day. So if you want to add a certain number of hours and minutes your formula would add a certain number of 1/1440's of a day.
e.g.
CreatedDate + 30/1440
Would be the formula for a custom formula field of datetime format to have a value 30 mins after the creation date. If you wanted to base this on other fields on the object you could then use:
CreatedDate + ((MyHours__c * 60) + MyMins__c)/1440
To add on your X hours and Y minutes. HTH
and going back to your original point, yes you could truncate the createddate to get the datevalue and then add on the additional time to get a time within working hours.
Hmm, hold fire on that thought, it's not happy adding the time to your original date formula. I'll update this when I fix it.
Nope, it's going to be much, much easier in Apex. The only way I can see a formula to do this will be to conver the CreatedDate to TEXT, use MID to grab the time element of the string, compare the string VALUE using IF statements to see if it's in working hours, and if not add/subtract the appropriate number of hours. Even then the value will be in UTC so you'll need to adjust for timezone and daylight saving.
As I said, time for a very quick Apex trigger to set a custom date/time field instead ?
Thanks for solution, I will study my problem with your answer.
I hate apex!! :)
I have to do test class and every time I have some problem with older trigger.
With formula the world is more easy! :)
The only function you can use for date/time is now().
You cannot achieve this today with formulas.
Vote for the idea: http://sites.force.com/ideaexchange/ideaView?c=09a30000000D9xt&id=08730000000BrJrAAK
I solve the problem thanks!!
In this way I send at 11 am of italy a email:
CASE(mid(text(field__c), 12,2), "20", field_date_time__c+(840/1440), "21", field_date_time__c + (780/1440), "22",field_date_time__c + (720/1440), "23",field_date_time__c + (660/1440), "00", field_date_time__c + (600/1440), "01",field_date_time__c + (540/1440), "02",field_date_time__c + (480/1440), "03", field_date_time__c + (420/1440), "04",field_date_time__c + (360/1440), "05",field_date_time__c + (300/1440), "06", field_date_time__c + (240/1440), "07",field_date_time__c + (180/1440), "08",field_date_time__c + (120/1440), field_date_time__c)
DATETIMEVALUE(DATE( year(DATEVALUE(CreatedDate)), month(DATEVALUE(CreatedDate)),day(DATEVALUE(CreatedDate)))) +3 + 17/24
DATE() gives me 00:00 UTC when converted to date/time using DATETIMEVALUE(). +3 adds three days to the result and +17/24 offsets the time to 10 AM UTC - 7 in my case.