LAKSHMAIAH CHAGANTI

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')))
Jason Curtis NBSFDG
What kind of data is stored in FLAGS__ViewedFlag__c, do you have an example?
LAKSHMAIAH CHAGANTI
DATA-Date/time Scenario-giving service to customers basing on priority.
Jason Curtis NBSFDG
A typical date time looks like this:
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.
Jason Curtis NBSFDG

Hi, Lakshmaiah, if the above helped you out, please mark this solved and best reply. Thanks.

Best,

Jason