Set Value Action for Calculated Field not displaying as Decimal

I have a set value action that is not setting the value as a decimal even though the data type is a decimal. I created a virtual column to do the calculation and I set the value to the vc as opposed to in the action but it still returns the whole number. When I input the start & end dates directly with a form, the calculation appears as expected.

Should I be formatting the calculation in the action somehow? or what could be converting the decimal to a whole number?

A million thanks

Calculation in action:
(HOUR([End Date Time]-[Start Date Time])*60+MINUTE([End Date Time]-[Start Date Time]))/60



First, just to make sure you are aware, the Hour() and Minute() functions will only return a Number type data, so you will never actually see partial hours.

Whenever a Number type is divided by another Number (e.g. Hour()/60) , the result will also be of Number type. To get a Decimal result, the easiest way is to divide by 60.0 - e.g. Hour()/60.0.

Since you are aiming for a Decimal result, I have to assume your are expecting partial Hours results. If so, then you really want to get the numerator into the total number of Minutes and then divide that by 60.0 to get the Decimal result in hours.

Additionally, if you are trying to calculate the number of hours, I’m not sure the calculation is correct. I could be wrong.

EDITED:

Upon further reflection, I see what you are doing now and believe it will work but think you can simplify this with a fairly recent function added:

TOTALMINUTES([End Date Time]-[Start Date Time])/60.0

OR even simpler

TOTALHOURS([End Date Time]-[Start Date Time])

Both of these return Decimal results so I assume you will get the desired partial hours with this last expression.

Be careful using only Time columns. If the times are from different days your calculation will not be accurate.

3 Likes

See also:

2 Likes

A million thanks John and Steve - you nailed it. Here is the final successful expression:

TEXT(DECIMAL((HOUR([End Date Time]-[Start Date Time])*60+MINUTE([End Date Time]-[Start Date Time])))/60)

Its a beautiful thing :slight_smile: Thanks again!
image

2 Likes