## 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!

## 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 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 on other sites
• 0

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```

##### 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 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.

## 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. ×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.

×   Your previous content has been restored.   Clear editor

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