Round up minutes to 15 minute increments for stopwatch

[DesiLogi]

Hello,

I have a number field ('Qty') in a submission form that is for recording the quantity of hours worked. So for example if someone worked 1 hour and 15 minutes the Qty value would be entered as 1.25. 

A new option I put in the submission form is a Start_Time field and an End_Time field (both Date/Time fields). A button running js puts the current date/time stamp in the Start_Time field and then when the user is done they click a button to put the new current date/time stamp in the End_Time field. The difference between the two  are the minutes worked, put in the Qty field via CalculatedField setting: Datediff(minute,[@field:Start_Time],[@field:End_Time])/60.00. This seems to work well so far. 

What I then need to do is Round Up to the nearest 15 minute value. For example, if the calculation for Start_Time 12/31/2019 11:45  and End_Time 12/31/2019 14:10  returns 2.416666 I need it to round up to 2.5. I've tried various Round() scenarios but can't get it right. 

Basically, I want the Qty value to always be a version of .25, .5, .75, 1, 1.25, 1.5, 1.75, 2, etc. Does anyone know how to use Round in the calculation above to do that? Many thanks!

[Kurumi]

Do you have your formula? You could try to check these links for Round function:

https://stackoverflow.com/questions/9873990/round-to-5-or-1-0-in-sql
https://www.sqlservercentral.com/forums/topic/rounding-to-the-nearest-50

I hope this helps.

[Hastur]

Hello @DesiLogi

Here is the example of the formula you may use:

CAST((SUBSTRING( CAST([@field:Numb_Val] AS nvarchar), 0, CHARINDEX('.', CAST([@field:Numb_Val] AS nvarchar)))) AS float)

+

CASE
WHEN 
CAST( ('0'+ SUBSTRING(CAST([@field:Numb_Val] AS nvarchar), CHARINDEX('.', CAST([@field:Numb_Val] AS nvarchar)), 100)) AS float) < 0.125 
THEN 0

WHEN 
CAST( ('0'+ SUBSTRING(CAST([@field:Numb_Val] AS nvarchar), CHARINDEX('.', CAST([@field:Numb_Val] AS nvarchar)), 100)) AS float) >= 0.125 
AND 
CAST( ('0'+ SUBSTRING(CAST([@field:Numb_Val] AS nvarchar), CHARINDEX('.', CAST([@field:Numb_Val] AS nvarchar)), 100)) AS float) < 0.375 
THEN 0.25

WHEN 
CAST( ('0'+ SUBSTRING(CAST([@field:Numb_Val] AS nvarchar), CHARINDEX('.', CAST([@field:Numb_Val] AS nvarchar)), 100)) AS float) >= 0.375 
AND
CAST( ('0'+ SUBSTRING(CAST([@field:Numb_Val] AS nvarchar), CHARINDEX('.', CAST([@field:Numb_Val] AS nvarchar)), 100)) AS float) < 0.625 
THEN 0.5

WHEN 
CAST( ('0'+ SUBSTRING(CAST([@field:Numb_Val] AS nvarchar), CHARINDEX('.', CAST([@field:Numb_Val] AS nvarchar)), 100)) AS float) >= 0.625 
AND 
CAST( ('0'+ SUBSTRING(CAST([@field:Numb_Val] AS nvarchar), CHARINDEX('.', CAST([@field:Numb_Val] AS nvarchar)), 100)) AS float) < 0.875 
THEN 0.75

WHEN 
CAST( ('0'+ SUBSTRING(CAST([@field:Numb_Val] AS nvarchar), CHARINDEX('.', CAST([@field:Numb_Val] AS nvarchar)) , 100)) AS float) >= 0.875 
THEN 1
END

In this formula, the DataType of the field you want to round is Number.
You need to change "[@field:Numb_Val]" part of the formula according to the name of the field you use.

[DesiLogi]

Thank you for the help- very much appreciated. 

Andrew's code worked well and I also put together a shorter version from Meekee's links and a little StackOverflow searching: 

(Round(((Datediff(minute,[@field:Start_Time],[@field:End_Time])/60.00)*4),0))/4

This will get a 15 minute increment into a number value rounded to .25 (meaning 1-7 minutes = 0,  8-22 minutes=.25,   23-37 minutes= .5,  38-52 minutes= .75,   53-67 minutes=1"). 

The only issue left is I need to Round Up (so that 1-15 minutes = .25, 16-30 minutes = .5,  31-45 minutes = .75,  46-60 minutes = 1). Right now the rounding will round down if the value is less than half the span. I tried using CEILING instead of ROUND (sql) but the calculated field won't take it. Does anyone know how to modify the above formula but to Round Up?

[Hastur]

@DesiLogi

You can apply the same logic as I did in the example
 

CASE

WHEN 
resul >= 1 
AND 
resul <= 15 
THEN 0.25
          
WHEN 
resul >= 16 
AND 
resul <= 30 
THEN 0.5
          
.
.
.

END

 

[DesiLogi]

Hi Andrew,

Thanks for the tip but doesn't that mean you'd have to list out all the criteria up to several hundred, in the same formula? Meaning 1-15, 16-30, 31-45, on up to over 1000? I

[DesiLogi]

In case anyone needs it, I have the solution, with Caspio's help, for Rounding Up to nearest 15 minute increments and then turning that into a numerical value: 

Add two (2) virtual fields which will consist of different formulas that will be used in another calculated field.

For the first virtual field,  the formula below will get the elapsed time of the Start_Time and End_Time:
(Datediff(minute,[@field:Start_Time], [@field:End_Time]))/60.00

For the second virtual field, the formula will get only the decimal places excluding the whole number:
CAST((Substring('[@cbParamVirtual1]', Charindex('.', '[@cbParamVirtual1]', 0), 3)) as FLOAT)

Then in the Qty (number) field (set to Calculated Value) use: 

(Datediff(minute,[@field:Start_Time], [@field:End_Time]))/60 +
CASE WHEN
[@cbParamVirtual2] <= 0.25 AND [@cbParamVirtual2] != 0
THEN .25
WHEN [@cbParamVirtual2] > 0.25 AND [@cbParamVirtual2] <= 0.5
THEN .50
WHEN [@cbParamVirtual2] > 0.50 AND [@cbParamVirtual2] <= 0.75
THEN .75WHEN [@cbParamVirtual2] > 0.75 AND [@cbParamVirtual2] <= 1
THEN 1
ELSE 0
END

For example, if elapsed time is 36 minutes it will get you 45 minutes and .75 instead of 30 minutes and .5.