BUT, I want to round that number to certain 15 minute increments, but not always up (Ceiling) and not only down (Floor). I'm struggling putting these two together.
Basically, if the minutes are 1 or 2 minutes over a 15 minute increment, then I want to round down. If the minutes are 3-14 minutes over a 15 minute increment, I want to round up.
Here are some examples of the desired output of the formula
14 minutes = .23 = Rounded UP to 0.25
16 minutes = .27 = Rounded DOWN to 0.25
17 minutes = .28 = Roundded DOWN to 0.25
18 minutes = .30 + Rounded UP to 0.50
3 Antworten
Not the most elegant solution, but I think it's the best. Used a case and some MOD math to get the correct decimals. It will require some maintenance if you change the rounding rules. Replace the "Time_Decimal__c" with your calculated time field. (CASE( CEILING((MOD(Time_Decimal__c ,1) * 60)),
1,0,
2,0,
3,0,
4,15,
5,15,
6,15,
7,15,
8,15,
9,15,
10,15,
11,15,
12,15,
13,15,
14,15,
15,15,
16,15,
17,15,
18,30,
19,30,
20,30,
21,30,
22,30,
23,30,
24,30,
25,30,
26,30,
27,30,
28,30,
29,30,
30,30,
31,30,
32,30,
33,45,
34,45,
35,45,
36,45,
37,45,
38,45,
39,45,
40,45,
41,45,
42,45,
43,45,
44,45,
45,45,
46,45,
47,45,
48,60,
49,60,
50,60,
51,60,
52,60,
53,60,
54,60,
55,60,
56,60,
57,60,
58,60,
59,60,
60) / 60) + FLOOR( Time_Decimal__c )