function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Mdglover5Mdglover5 

Updating a date field based on date/time field.

Hi all, 

 

I have a date field which is a date formula field based on a date/time field. This works correctly, but I would like to add a bit of extra functionality. My company has an SLA that anything after 5pm should be treated as being recieved the next day. Therefore I would like the date formula field to work as below:

 

If the date/time is 17/09/2012 12:56 the formula field will display 17/09/12.

If the date/time is 17/09/2012 17:30 the formula field will display 18/09/12.

 

Any ideas/help/solutions would be greatly appreciated especially as it is my first post!

 

Thanks

 

Matt

 

Best Answer chosen by Admin (Salesforce Developers) 
jd123jd123

Sorry hour is not a global function 

 

here is the code but you can set the time

 

if(CASE( VALUE(MID(TEXT( Planned_Start_DateTime__c ),12,2)) ,
00, 00,
01, 01,
02, 02,
03, 03,
04, 04,
05, 05,
06, 06,
07, 07,
08, 08,
09, 09,
10, 10,
11, 11,
12, 12,
13, 13,
14, 14,
15, 15,
16, 16,
17, 17,
18, 18,
19, 19,
20, 20,
21, 21,
22, 22,
23, 23,
0)>17, DATEVALUE(Planned_Start_DateTime__c ),DATEVALUE(Planned_Start_DateTime__c )+1)

All Answers

jd123jd123

hi

return type Date

if(hour(date1__c)>17,Date1__c,Date1__c+1)
Mdglover5Mdglover5

Hi jd, 

 

Trying this using the date/time opened field and getting the following error:

 

Error: Invalid Data. 
Review all error messages below to correct your data.
Unknown function hour. Check spelling.

 

When I am entering on the case object. :

 

if(hour(CreatedDate)>17,Date1__c,Date1__c+1)

 

Any suggestions?

 
jd123jd123

Sorry hour is not a global function 

 

here is the code but you can set the time

 

if(CASE( VALUE(MID(TEXT( Planned_Start_DateTime__c ),12,2)) ,
00, 00,
01, 01,
02, 02,
03, 03,
04, 04,
05, 05,
06, 06,
07, 07,
08, 08,
09, 09,
10, 10,
11, 11,
12, 12,
13, 13,
14, 14,
15, 15,
16, 16,
17, 17,
18, 18,
19, 19,
20, 20,
21, 21,
22, 22,
23, 23,
0)>17, DATEVALUE(Planned_Start_DateTime__c ),DATEVALUE(Planned_Start_DateTime__c )+1)

This was selected as the best answer
Mdglover5Mdglover5

That works thanks. My developer came up with a similar formula that worked as well:

 

if( VALUE(MID(TEXT( SLA_Response_Time__c ),12,2) )   <17, DATEVALUE(SLA_Response_Time__c  ),DATEVALUE(SLA_Response_Time__c  )+1)

 

Thanks for all your help.

 

Matt