Apex Code Development You need to sign in to do that
Don't have an account?
GRStevenBrookes At Wits end with Date/Time Calc
Morning All,
Ok, so for the past 3 hours I have been at this. But cant seem to get it working so that both myself and Salesforce UI is happy!
Essentially, I am running a Workflow Field Update to insert the Date and Time, something is 'due', the value of when this is is dependent on the day and the time of the day. For example Between 9am and 3pm Monday-Thursday, the due date is in an hours time, another example, on a Saturday, the due date is 9am on Monday (same for sunday). So, I first looked into how to get add and subtract time from a Now() value and thanks to some well documented support, I realised it was a simple calculation, i.e to add 1 hour you would use something like this Now() + (1/24), so I went and laid out my fomula - without realising it wouldnt fit in the field on the UI so this wouldnt work:
/////OLD FULL DETAIL FORMULA - TOO LONG FOR SALESFORCE////// IF( AND( OR( DEBUG_SACreatedDay__c = "Monday", DEBUG_SACreatedDay__c = "Tuesday", DEBUG_SACreatedDay__c = "Wednesday", DEBUG_SACreatedDay__c = "Thursday", DEBUG_SACreatedDay__c = "Friday"), OR( BEGINS(DEBUG_SAHourCreated__c,"09"), BEGINS(DEBUG_SAHourCreated__c,"10"), BEGINS(DEBUG_SAHourCreated__c,"11"), BEGINS(DEBUG_SAHourCreated__c,"12"), BEGINS(DEBUG_SAHourCreated__c,"13"), BEGINS(DEBUG_SAHourCreated__c,"14"), BEGINS(DEBUG_SAHourCreated__c,"15"), BEGINS(DEBUG_SAHourCreated__c,"16"))), NOW()+ (1/24), IF( AND( OR( DEBUG_SACreatedDay__c = "Monday", DEBUG_SACreatedDay__c = "Tuesday", DEBUG_SACreatedDay__c = "Wednesday", DEBUG_SACreatedDay__c = "Thursday"), BEGINS(DEBUG_SAHourCreated__c,"17")), NOW()-(1/3)+1, IF( AND( OR( DEBUG_SACreatedDay__c = "Monday", DEBUG_SACreatedDay__c = "Tuesday", DEBUG_SACreatedDay__c = "Wednesday", DEBUG_SACreatedDay__c = "Thursday"), BEGINS(DEBUG_SAHourCreated__c,"18")), NOW()-(1/2.66)+1, IF( AND( OR( DEBUG_SACreatedDay__c = "Monday", DEBUG_SACreatedDay__c = "Tuesday", DEBUG_SACreatedDay__c = "Wednesday", DEBUG_SACreatedDay__c = "Thursday"), BEGINS(DEBUG_SAHourCreated__c,"19")), NOW()-(1/2.4)+1, IF( AND( OR( DEBUG_SACreatedDay__c = "Monday", DEBUG_SACreatedDay__c = "Tuesday", DEBUG_SACreatedDay__c = "Wednesday", DEBUG_SACreatedDay__c = "Thursday"), BEGINS(DEBUG_SAHourCreated__c,"20")), NOW()-(1/2.18)+1, IF( AND( OR( DEBUG_SACreatedDay__c = "Monday", DEBUG_SACreatedDay__c = "Tuesday", DEBUG_SACreatedDay__c = "Wednesday", DEBUG_SACreatedDay__c = "Thursday"), BEGINS(DEBUG_SAHourCreated__c,"21")), NOW()-(1/2)+1, IF( AND( OR( DEBUG_SACreatedDay__c = "Monday", DEBUG_SACreatedDay__c = "Tuesday", DEBUG_SACreatedDay__c = "Wednesday", DEBUG_SACreatedDay__c = "Thursday"), BEGINS(DEBUG_SAHourCreated__c,"22")), NOW()-(1/1.84)+1, IF( AND( OR( DEBUG_SACreatedDay__c = "Monday", DEBUG_SACreatedDay__c = "Tuesday", DEBUG_SACreatedDay__c = "Wednesday", DEBUG_SACreatedDay__c = "Thursday"), BEGINS(DEBUG_SAHourCreated__c,"23")), NOW()-(1/1.71)+1, IF( AND( OR( DEBUG_SACreatedDay__c = "Monday", DEBUG_SACreatedDay__c = "Tuesday", DEBUG_SACreatedDay__c = "Wednesday", DEBUG_SACreatedDay__c = "Thursday"), BEGINS(DEBUG_SAHourCreated__c,"00")), NOW()+(1/2.66), IF( AND( OR( DEBUG_SACreatedDay__c = "Monday", DEBUG_SACreatedDay__c = "Tuesday", DEBUG_SACreatedDay__c = "Wednesday", DEBUG_SACreatedDay__c = "Thursday"), BEGINS(DEBUG_SAHourCreated__c,"01")), NOW()+(1/3), IF( AND( OR( DEBUG_SACreatedDay__c = "Monday", DEBUG_SACreatedDay__c = "Tuesday", DEBUG_SACreatedDay__c = "Wednesday", DEBUG_SACreatedDay__c = "Thursday"), BEGINS(DEBUG_SAHourCreated__c,"02")), NOW()+(1/3.42), IF( AND( OR( DEBUG_SACreatedDay__c = "Monday", DEBUG_SACreatedDay__c = "Tuesday", DEBUG_SACreatedDay__c = "Wednesday", DEBUG_SACreatedDay__c = "Thursday"), BEGINS(DEBUG_SAHourCreated__c,"03")), NOW()+(1/4), IF( AND( OR( DEBUG_SACreatedDay__c = "Monday", DEBUG_SACreatedDay__c = "Tuesday", DEBUG_SACreatedDay__c = "Wednesday", DEBUG_SACreatedDay__c = "Thursday"), BEGINS(DEBUG_SAHourCreated__c,"04")), NOW()+(1/4.8), IF( AND( OR( DEBUG_SACreatedDay__c = "Monday", DEBUG_SACreatedDay__c = "Tuesday", DEBUG_SACreatedDay__c = "Wednesday", DEBUG_SACreatedDay__c = "Thursday"), BEGINS(DEBUG_SAHourCreated__c,"05")), NOW()+(1/6), IF( AND( OR( DEBUG_SACreatedDay__c = "Monday", DEBUG_SACreatedDay__c = "Tuesday", DEBUG_SACreatedDay__c = "Wednesday", DEBUG_SACreatedDay__c = "Thursday"), BEGINS(DEBUG_SAHourCreated__c,"06")), NOW()+(1/8), IF( AND( OR( DEBUG_SACreatedDay__c = "Monday", DEBUG_SACreatedDay__c = "Tuesday", DEBUG_SACreatedDay__c = "Wednesday", DEBUG_SACreatedDay__c = "Thursday"), BEGINS(DEBUG_SAHourCreated__c,"07")), NOW()+(1/12), IF( AND( OR( DEBUG_SACreatedDay__c = "Monday", DEBUG_SACreatedDay__c = "Tuesday", DEBUG_SACreatedDay__c = "Wednesday", DEBUG_SACreatedDay__c = "Thursday"), BEGINS(DEBUG_SAHourCreated__c,"08")), NOW()+(1/24), IF( AND( DEBUG_SACreatedDay__c = "Friday", BEGINS(DEBUG_SAHourCreated__c,"17")), NOW()-(1/3)+3, IF( AND( DEBUG_SACreatedDay__c = "Friday", BEGINS(DEBUG_SAHourCreated__c,"18")), NOW()-(1/2.66)+3, IF( AND( DEBUG_SACreatedDay__c = "Friday", BEGINS(DEBUG_SAHourCreated__c,"19")), NOW()-(1/2.4)+3, IF( AND( DEBUG_SACreatedDay__c = "Friday", BEGINS(DEBUG_SAHourCreated__c,"20")), NOW()-(1/2.18)+3, IF( AND( DEBUG_SACreatedDay__c = "Friday", BEGINS(DEBUG_SAHourCreated__c,"21")), NOW()-(1/2)+3, IF( AND( DEBUG_SACreatedDay__c = "Friday", BEGINS(DEBUG_SAHourCreated__c,"22")), NOW()-(1/1.84)+3, IF( AND( DEBUG_SACreatedDay__c = "Friday", BEGINS(DEBUG_SAHourCreated__c,"23")), NOW()-(1/1.71)+3, IF( AND( DEBUG_SACreatedDay__c = "Monday", BEGINS(DEBUG_SAHourCreated__c,"00")), NOW()+ (1/2.6), IF( AND( DEBUG_SACreatedDay__c = "Monday", BEGINS(DEBUG_SAHourCreated__c,"01")), NOW()+ (1/3), IF( AND( DEBUG_SACreatedDay__c = "Monday", BEGINS(DEBUG_SAHourCreated__c,"02")), NOW()+ (1/3.42), IF( AND( DEBUG_SACreatedDay__c = "Monday", BEGINS(DEBUG_SAHourCreated__c,"03")), NOW()+ (1/4), IF( AND( DEBUG_SACreatedDay__c = "Monday", BEGINS(DEBUG_SAHourCreated__c,"04")), NOW()+ (1/4.8), IF( AND( DEBUG_SACreatedDay__c = "Monday", BEGINS(DEBUG_SAHourCreated__c,"05")), NOW()+ (1/6), IF( AND( DEBUG_SACreatedDay__c = "Monday", BEGINS(DEBUG_SAHourCreated__c,"06")), NOW()+ (1/8), IF( AND( DEBUG_SACreatedDay__c = "Monday", BEGINS(DEBUG_SAHourCreated__c,"07")), NOW()+ (1/12), IF( AND( DEBUG_SACreatedDay__c = "Monday", BEGINS(DEBUG_SAHourCreated__c,"08")), NOW()+ (1/24), IF( AND( DEBUG_SACreatedDay__c = "Monday", BEGINS(DEBUG_SAHourCreated__c,"09")), NOW()+ (1/24), IF( AND( DEBUG_SACreatedDay__c = "Saturday", BEGINS(DEBUG_SAHourCreated__c,"00")), NOW()+ (1/2.6)+2, IF( AND( DEBUG_SACreatedDay__c = "Saturday", BEGINS(DEBUG_SAHourCreated__c,"01")), NOW()+ (1/3)+2, IF( AND( DEBUG_SACreatedDay__c = "Saturday", BEGINS(DEBUG_SAHourCreated__c,"02")), NOW()+ (1/3.42)+2, IF( AND( DEBUG_SACreatedDay__c = "Saturday", BEGINS(DEBUG_SAHourCreated__c,"03")), NOW()+ (1/4)+2, IF( AND( DEBUG_SACreatedDay__c = "Saturday", BEGINS(DEBUG_SAHourCreated__c,"04")), NOW()+ (1/4.8)+2, IF( AND( DEBUG_SACreatedDay__c = "Saturday", BEGINS(DEBUG_SAHourCreated__c,"05")), NOW()+ (1/6)+2, IF( AND( DEBUG_SACreatedDay__c = "Saturday", BEGINS(DEBUG_SAHourCreated__c,"06")), NOW()+ (1/8)+2, IF( AND( DEBUG_SACreatedDay__c = "Saturday", BEGINS(DEBUG_SAHourCreated__c,"07")), NOW()+ (1/12)+2, IF( AND( DEBUG_SACreatedDay__c = "Saturday", BEGINS(DEBUG_SAHourCreated__c,"08")), NOW()+ (1/24)+2, IF( AND( DEBUG_SACreatedDay__c = "Saturday", BEGINS(DEBUG_SAHourCreated__c,"09")), NOW() +2, IF( AND( DEBUG_SACreatedDay__c = "Saturday", BEGINS(DEBUG_SAHourCreated__c,"10")), NOW()- (1/24)+2, IF( AND( DEBUG_SACreatedDay__c = "Saturday", BEGINS(DEBUG_SAHourCreated__c,"11")), NOW()-(1/12)+2, IF( AND( DEBUG_SACreatedDay__c = "Saturday", BEGINS(DEBUG_SAHourCreated__c,"12")), NOW()-(1/8)+2, IF( AND( DEBUG_SACreatedDay__c = "Saturday", BEGINS(DEBUG_SAHourCreated__c,"13")), NOW()- (1/6)+2, IF( AND( DEBUG_SACreatedDay__c = "Saturday", BEGINS(DEBUG_SAHourCreated__c,"14")), NOW()-(1/4.8)+2, IF( AND( DEBUG_SACreatedDay__c = "Saturday", BEGINS(DEBUG_SAHourCreated__c,"15")), NOW()-(1/4)+2, IF( AND( DEBUG_SACreatedDay__c = "Saturday", BEGINS(DEBUG_SAHourCreated__c,"16")), NOW()-(1/3.42)+2, IF( AND( DEBUG_SACreatedDay__c = "Saturday", BEGINS(DEBUG_SAHourCreated__c,"17")), NOW()-(1/3)+2, IF( AND( DEBUG_SACreatedDay__c = "Saturday", BEGINS(DEBUG_SAHourCreated__c,"18")), NOW()-(1/2.66)+2, IF( AND( DEBUG_SACreatedDay__c = "Saturday", BEGINS(DEBUG_SAHourCreated__c,"19")), NOW()-(1/2.4)+2, IF( AND( DEBUG_SACreatedDay__c = "Saturday", BEGINS(DEBUG_SAHourCreated__c,"20")), NOW()-(1/2.18)+2, IF( AND( DEBUG_SACreatedDay__c = "Saturday", BEGINS(DEBUG_SAHourCreated__c,"21")), NOW()-(1/2)+2, IF( AND( DEBUG_SACreatedDay__c = "Saturday", BEGINS(DEBUG_SAHourCreated__c,"22")), NOW()-(1/1.84)+2, IF( AND( DEBUG_SACreatedDay__c = "Saturday", BEGINS(DEBUG_SAHourCreated__c,"23")), NOW()-(1/1.71)+2, IF( AND( DEBUG_SACreatedDay__c = "Sunday", BEGINS(DEBUG_SAHourCreated__c,"00")), NOW()+ (1/2.6)+1, IF( AND( DEBUG_SACreatedDay__c = "Sunday", BEGINS(DEBUG_SAHourCreated__c,"01")), NOW()+ (1/3)+1, IF( AND( DEBUG_SACreatedDay__c = "Sunday", BEGINS(DEBUG_SAHourCreated__c,"02")), NOW()+ (1/3.42)+1, IF( AND( DEBUG_SACreatedDay__c = "Sunday", BEGINS(DEBUG_SAHourCreated__c,"03")), NOW()+ (1/4)+1, IF( AND( DEBUG_SACreatedDay__c = "Sunday", BEGINS(DEBUG_SAHourCreated__c,"04")), NOW()+ (1/4.8)+1, IF( AND( DEBUG_SACreatedDay__c = "Sunday", BEGINS(DEBUG_SAHourCreated__c,"05")), NOW()+ (1/6)+1, IF( AND( DEBUG_SACreatedDay__c = "Sunday", BEGINS(DEBUG_SAHourCreated__c,"06")), NOW()+ (1/8)+1, IF( AND( DEBUG_SACreatedDay__c = "Sunday", BEGINS(DEBUG_SAHourCreated__c,"07")), NOW()+ (1/12)+1, IF( AND( DEBUG_SACreatedDay__c = "Sunday", BEGINS(DEBUG_SAHourCreated__c,"08")), NOW()+ (1/24)+1, IF( AND( DEBUG_SACreatedDay__c = "Sunday", BEGINS(DEBUG_SAHourCreated__c,"09")), NOW() +1, IF( AND( DEBUG_SACreatedDay__c = "Sunday", BEGINS(DEBUG_SAHourCreated__c,"10")), NOW()- (1/24)+1, IF( AND( DEBUG_SACreatedDay__c = "Sunday", BEGINS(DEBUG_SAHourCreated__c,"11")), NOW()-(1/12)+1, IF( AND( DEBUG_SACreatedDay__c = "Sunday", BEGINS(DEBUG_SAHourCreated__c,"12")), NOW()-(1/8)+1, IF( AND( DEBUG_SACreatedDay__c = "Sunday", BEGINS(DEBUG_SAHourCreated__c,"13")), NOW()- (1/6)+1, IF( AND( DEBUG_SACreatedDay__c = "Sunday", BEGINS(DEBUG_SAHourCreated__c,"14")), NOW()-(1/4.8)+1, IF( AND( DEBUG_SACreatedDay__c = "Sunday", BEGINS(DEBUG_SAHourCreated__c,"15")), NOW()-(1/4)+1, IF( AND( DEBUG_SACreatedDay__c = "Sunday", BEGINS(DEBUG_SAHourCreated__c,"16")), NOW()-(1/3.42)+1, IF( AND( DEBUG_SACreatedDay__c = "Sunday", BEGINS(DEBUG_SAHourCreated__c,"17")), NOW()-(1/3)+1, IF( AND( DEBUG_SACreatedDay__c = "Sunday", BEGINS(DEBUG_SAHourCreated__c,"18")), NOW()-(1/2.66)+1, IF( AND( DEBUG_SACreatedDay__c = "Sunday", BEGINS(DEBUG_SAHourCreated__c,"19")), NOW()-(1/2.4)+1, IF( AND( DEBUG_SACreatedDay__c = "Sunday", BEGINS(DEBUG_SAHourCreated__c,"20")), NOW()-(1/2.18)+1, IF( AND( DEBUG_SACreatedDay__c = "Sunday", BEGINS(DEBUG_SAHourCreated__c,"21")), NOW()-(1/2)+1, IF( AND( DEBUG_SACreatedDay__c = "Sunday", BEGINS(DEBUG_SAHourCreated__c,"22")), NOW()-(1/1.84)+1, IF( AND( DEBUG_SACreatedDay__c = "Sunday", BEGINS(DEBUG_SAHourCreated__c,"23")), NOW()-(1/1.71)+1), NOW() + (1/24) )))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
So i then thought, how do i shorten this, so decided to put the decimal/fraction part into its own field using a CASE statement like this:
CASE"(VALUE"(LEFT"( DEBUG_SAHourCreated__c,2)")", 0,2.66, 1,3.0, 2,3.42, 3,4.0, 4,4.8, 5,6.0, 6,8.0, 7,12, 8,24, 9,0, 10,24, 11,12, 12,8, 13,6, 14,4.8, 15,4, 16,3.42, 17,3, 18,2.66, 19,2.4, 20,2.18, 21,2.0, 22,1.84, 23,1.71, 0)"
and then re-wrote the orginial formula to reference this (a lot shorter in size!):
IF( AND( VALUE(LEFT( DEBUG_SAHourCreated__c,2))>=9, VALUE(LEFT( DEBUG_SAHourCreated__c,2))<=15, OR( DEBUG_SACreatedDay__c = "Monday", DEBUG_SACreatedDay__c = "Tuesday", DEBUG_SACreatedDay__c = "Wednesday", DEBUG_SACreatedDay__c = "Thursday", DEBUG_SACreatedDay__c = "Friday")), NOW()+ (1/24), IF( AND( VALUE(LEFT( DEBUG_SAHourCreated__c,2))>15, VALUE(LEFT( DEBUG_SAHourCreated__c,2))<24, OR( DEBUG_SACreatedDay__c = "Monday", DEBUG_SACreatedDay__c = "Tuesday", DEBUG_SACreatedDay__c = "Wednesday", DEBUG_SACreatedDay__c = "Thursday")), NOW()-(DEBUG_WC_Time_Decimal__c)+1, IF( AND( VALUE(LEFT( DEBUG_SAHourCreated__c,2))>=0, VALUE(LEFT( DEBUG_SAHourCreated__c,2))<9, OR( DEBUG_SACreatedDay__c = "Monday", DEBUG_SACreatedDay__c = "Tuesday", DEBUG_SACreatedDay__c = "Wednesday", DEBUG_SACreatedDay__c = "Thursday", DEBUG_SACreatedDay__c = "Friday")), NOW()+(DEBUG_WC_Time_Decimal__c), IF( AND( VALUE(LEFT( DEBUG_SAHourCreated__c,2))>15, VALUE(LEFT( DEBUG_SAHourCreated__c,2))<24, DEBUG_SACreatedDay__c = "Friday"), NOW()-(DEBUG_WC_Time_Decimal__c)+3, IF( AND( VALUE(LEFT( DEBUG_SAHourCreated__c,2))>15, VALUE(LEFT( DEBUG_SAHourCreated__c,2))<24, DEBUG_SACreatedDay__c = "Saturday"), NOW()-(DEBUG_WC_Time_Decimal__c)+2, IF( AND( VALUE(LEFT( DEBUG_SAHourCreated__c,2))>=0, VALUE(LEFT( DEBUG_SAHourCreated__c,2))<9, DEBUG_SACreatedDay__c = "Saturday"), NOW()+(DEBUG_WC_Time_Decimal__c)+2, IF( AND( VALUE(LEFT( DEBUG_SAHourCreated__c,2))>15, VALUE(LEFT( DEBUG_SAHourCreated__c,2))<24, DEBUG_SACreatedDay__c = "Sunday"), NOW()-(DEBUG_WC_Time_Decimal__c)+1, IF( AND( VALUE(LEFT( DEBUG_SAHourCreated__c,2))>=0, VALUE(LEFT( DEBUG_SAHourCreated__c,2))<9, DEBUG_SACreatedDay__c = "Sunday"), NOW()+(DEBUG_WC_Time_Decimal__c)+1, NOW()))))))))
however!!!!! using this method, SF calculates in a different way - instead of doing Now() + (1/24) + 1 (for example) it does Now() + 0.041 + 1, which gives a totally different answer, in fact SF just adds 1.041 days onto the Now().
Can any one offer any help here? Either utilising the hard work I have put in above, or any other way to acheive what I am trying to achieve.
Many thanks in advance, sorry for the long post!
Steve
Due to the complicated logic - and the inclusion of business hours / days in the calc, I would attack it with the "Business Hours" and "Holidays" functions within the UI and then an APEX trigger.
-Andy