+ Start a Discussion
mat_tone_84mat_tone_84 

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?

Best Answer chosen by Admin (Salesforce Developers) 
EnthEnth

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

EnthEnth
You're on the right track, but don't assume you can schedule emails to occur at exact times, i.e. to the minute. Time based workflows, and even scheduled Apex, do not fire like Unix Cron jobs at a specific time, they fire as close to it as Force.Com can given the vast number of scheduled tasks it's trying to process.
mat_tone_84mat_tone_84

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

 

 

EnthEnth

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

This was selected as the best answer
EnthEnth

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.

Message Edited by Enth on 21-03-2010 05:06 PM
EnthEnth

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 ?

 

 

 

 

 

Message Edited by Enth on 21-03-2010 05:29 PM
mat_tone_84mat_tone_84

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! :)

marco_29marco_29

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 

mat_tone_84mat_tone_84

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)

 

 

 

Eric Ishikawa 10Eric Ishikawa 10
I don't think DATETMEVALUE() was available back in 2010.  Here is how I acomplished this today. 

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. 
Marco HeinrichMarco Heinrich
Thank you for this very usefull hint to ad hours to an existing date/time field :-)