I'm ultimately trying to get a duration and a...

(Nathan McGregor) #1

I’m ultimately trying to get a duration and a decimal to interact but it’s not working:

I’ve set up two virtual columns:

  1. Converts a duration to a decimal: =Hour([Hours worked at First job])+Minute([Hours worked at First job])/60

  2. Subtracts two decimals to produce one (this is more of an error check): =([First machine finish hours] - [First machine start hours])

The ‘apparently’ successful outcome can be seen in the screenshot below.

The problem is that when I subtract these two numbers I get this:

=[NumHoursJob1] - [NumMachineHoursJ1] =4.51

Even worse is that if the duration [NumHoursJob1] is a round number such as 4, it will display ‘40’.

Can someone please help me explain the cause and solution?

(Suvrutt Gurjar) #2

Hi @Nathan_McGregor, You may wish to try the expression for 1 above

=Hour([Hours worked at First job])+Minute([Hours worked at First job])/60.00

(Nathan McGregor) #3

Thanks for the reply. I tried it and the same problem persists.

Information that may be helpful is:

Case 1 NumHoursJob1 = 1 (from duration 1:00:00) NumMachineHoursJ1 = 0 [NumHoursJob1] - [NumMachineHoursJ1] = 10

Case 2- NumHoursJob1 = 1 (from duration 1:00:00) NumMachineHoursJ1 = 4 [NumHoursJob1] - [NumMachineHoursJ1] = 14

Case 3 NumHoursJob1 = 1.5 (from duration 1:30:00 NumMachineHoursJ1 = 4 [NumHoursJob1] - [NumMachineHoursJ1] = 1.54

It’s basically just adding a digit for [NumMachineHoursJ1] but I don’t know why.

(Suvrutt Gurjar) #4

Hi @Nathan_McGregor,Thank you for your more inputs. I did some testing with 2 decimal type numbers, one composed with HOUR and MINUTE components and the other with just a fixed number.

I added these two numbers and these are getting added as expected.

I will share some screenshots.

Column 1 :[TestDurHr]

Type:

Decimal

(Suvrutt Gurjar) #5

Column 2: {DurAddNumber]

Type: Decimal

(Suvrutt Gurjar) #6

Test results for the addition

Hope all the screenshots and tests help you in some way.