Jump to content
  • 0
DesiLogi

Round up minutes to 15 minute increments for stopwatch

Question

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!

Share this post


Link to post
Share on other sites

6 answers to this question

Recommended Posts

  • 0

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.

Share this post


Link to post
Share on other sites
  • 0

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?

Share this post


Link to post
Share on other sites
  • 0

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

Share this post


Link to post
Share on other sites
  • 0

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. 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...