Skip to main content
I need to calculate ROUNDED time in 15 minutes increments. I created a simple forumla field that calculates the total time between two Date/Time fields, which gives me a decimal number, which I like so I can use this number in rollups and summary reports. So 2 hours and 32 minutes = 2.53. I am okay using this field as a part of another formula field that does the rounding.

 

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 respostas
  1. 1 de set. de 2017, 19:56
    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 )