+ Start a Discussion
Brian Roberts 2Brian Roberts 2 

Formula for breaking a 24 hour day into 15 minute increments.

I would like to divide a 24 hour day into 15 minute increments so a time worked can be selected from a picklist value and multiplied by the daily rate to get a total cost. the picklist is broken down into 15 minute increments as follows 00:15, 00:30, 00:45, 01:00, 01:15 and so on. Can anyonr advise me on the best way to do this?
mitsvikmitsvik
hello were you able to figure this out?
mitsvikmitsvik
cause i am looking to do this...
Brian Roberts 2Brian Roberts 2
I broke it down to hours of the day but not 15 minute intervals.
mitsvikmitsvik
Can you share here with me the code and the way you did it.  I have a weird issue, that i am gettig time interval for 30 min based on a request time, but they wantto see time intervals that there are no data also , for example say 00:00 times and 00:30 times....can you help...
Brian Roberts 2Brian Roberts 2
To calculate the partial days the following formula was used:
if(CONTAINS(TEXT( Date_Start_Time__c),":"), -1 +(24-value(left(text(Date_Start_Time__c),2)))/24 ,0)
+
if(CONTAINS(TEXT( Date_End_Time__c),":"), -1 + value(left(text(Date_End_Time__c),2))/24 ,0)

Then to calculate the total the following was used:

MAX(Date2_End__c + 1 - Date2_Start__c + Calc_Qty_Partial_Days_Formula__c,0)
Brian Roberts 2Brian Roberts 2
The times were a picklist field that was added breaking the day down to 24 segments by hours, be careful as you may exceed the formula allowance taking all into account.
mitsvikmitsvik
Hello Brian, I am not sure i understand your formula. But, i need to get the time you see below. I have nailed it to actually get the times in that order by using request times. But say there is a no request time at around :45 past midnight, i should have that  time slot but still show the rows as 0. Is that possibel?
 User-added image
Brian Roberts 2Brian Roberts 2
To be honest I am not sure I got to the hourly breakdown and was stumped myself given that the formula wa too large taking into account all the other items over the time span and pro ratatering these to combine into totals. the formula is quite simple you have a pick list with your times, these are chosen by the person compiling the ticket, if these fields are used then the formula takes them into account and pro ratas the day, configuratively speaking you should be able to do this with 15 minute increments providing you change the constant  figures in the formula.