You need to sign in to do that
Don't have an account?

TYPICAL FORMULA TO SERVE CUSTOMERS ON TIME BASED
Guys, Will you please elaborate the following formula. We are using this formula for clearing cases of customers on priority base. As I have very basic knowledge on formula, it's a little bit hard to me to understand this formula.
RPAD(
RPAD(
RPAD(
text(CASE(VALUE(TRIM(LEFT(TRIM(RIGHT(TEXT(FLAGS__ViewedFlag__c),9)),2))),
13,13,
12,13,
11,13,
10,13,
9,13,
8,13,
7,13,
6,13,
5,22,
4,22,
3,22,
2,22,
1,22,
0,22,
24,22,
23,22,
VALUE(TRIM(LEFT(TRIM(RIGHT(TEXT(FLAGS__ViewedFlag__c),9)),2)))))
,3,":"),
5,
IF(VALUE(TRIM(LEFT(TRIM(RIGHT(TEXT(FLAGS__ViewedFlag__c),9)),2)))>=22,
'00',
IF(VALUE(TRIM(LEFT(TRIM(RIGHT(TEXT(FLAGS__ViewedFlag__c),9)),2)))<13,
'00',
TRIM(RIGHT(TRIM(LEFT(TEXT(FLAGS__ViewedFlag__c),16)),2))))),8,
':00')))
RPAD(
RPAD(
RPAD(
text(CASE(VALUE(TRIM(LEFT(TRIM(RIGHT(TEXT(FLAGS__ViewedFlag__c),9)),2))),
13,13,
12,13,
11,13,
10,13,
9,13,
8,13,
7,13,
6,13,
5,22,
4,22,
3,22,
2,22,
1,22,
0,22,
24,22,
23,22,
VALUE(TRIM(LEFT(TRIM(RIGHT(TEXT(FLAGS__ViewedFlag__c),9)),2)))))
,3,":"),
5,
IF(VALUE(TRIM(LEFT(TRIM(RIGHT(TEXT(FLAGS__ViewedFlag__c),9)),2)))>=22,
'00',
IF(VALUE(TRIM(LEFT(TRIM(RIGHT(TEXT(FLAGS__ViewedFlag__c),9)),2)))<13,
'00',
TRIM(RIGHT(TRIM(LEFT(TEXT(FLAGS__ViewedFlag__c),16)),2))))),8,
':00')))
2014-01-08 20:02:47Z
http://www.salesforce.com/us/developer/docs/officetoolkit/Content/sforce_api_calls_soql_select_dateformats.htm
This formula takes the date/time field, turns it into a text, strips off all leading characters except for the last 9, leaving you with just the time (from the date).
Then it strips the the minutes and seconds off of the time just leaving the hours.
Then it converts the text back to a number (with VALUE).
The it evaluates the hour and coverts it to a new desired number (using CASE), so if first number, then make it the second number.
13,13,
12,13,
11,13,
10,13,
9,13,
8,13,
7,13,
6,13,
5,22,
4,22,
3,22,
2,22,
1,22,
0,22,
24,22,
23,22
(either 13 or 22 or else just the hour if it didn't match (i.e. was >13 & <=22)
It takes the evaluated number and turns it back to text.
And it starts to pad it (RPAD) with the other necessary data for the final output, and and does a couple of ifs for data > 12 <=22 to insert 00 in the final output.
It also takes the year and uses it in the if statement and building of the output.
THe underlying business reasons for the formula are a little vaguage. Ideally you would actually have the values you use to evaulate the CASE in a setting somwhere so that it could be changes with out having to parse this formula.
Hope this helps.
Hi, Lakshmaiah, if the above helped you out, please mark this solved and best reply. Thanks.
Best,
Jason