# Formula for subtracting two work hours fields. One has an h in it and the other does not • ### Question

• Hello,

I am looking to replicate two formulas being used in excel and have those formulas in MS Project itself using a number 10 field.

1. One simply subtracts Number5 from Actual work. =IF(F2<>0,(SUM(F2-G2)))

Looks like the issue in MS Project is that Number5 is an estimate in hours and has no appended H but actual work does

what would that formula look like in MS Project?

2. The other multiplies Number5*.9 and then subtracts actual work. =IF(F2<>0,(SUM(F2*0.9)-(G2)))

What would that formula look like in MS Project

Thanks

Tuesday, January 7, 2020 10:46 PM

• Hotmail1,

The "hrs" is simply something Project adds to the visual display, the actual work itself is just a number, but, like all time related values in Project, internally it is stored in minutes. The following formula should work in the first case

Number2=[Actual Work]/60-[Number5]

The second formula will be very similar

Number3=[Number5]*0.9-[Actual Work]/60

Hope this helps.

John

• Marked as answer by Thursday, January 9, 2020 4:31 AM
Wednesday, January 8, 2020 2:28 AM
• Hotmail1,

I didn't know what to make of your "if" statement since it referred to Excel rows and columns with no explanation of what those were supposed to be in Project. And it's not clear how the "SUM" should be handled, or what the formula should produce if Number5 (G2?) is not zero?

I need a more complete definition of what you want.

With respect to the second issue of not seeming to handle decimal values, this is what the formula produces in my test case (If condition added as shown) John

• Marked as answer by Thursday, January 9, 2020 4:31 AM
Wednesday, January 8, 2020 4:58 PM
• Hotmail1,

That's interesting. No, there is no setting that should cause the rounding. What version of Project are you using again?

Try this, instead of using custom Number fields, try using a couple of text fields. In the screen shot below I use Text29 for the BOE hrs and Text30 for the formula

Text30 = IIf([Text29]<>0,[Text29]-[Actual Work]/60,0)

Does that work for you? If that doesn't work, here's another idea. Use this formula

Number10=IIf([Number5]<>0,([Number5]*60-[Actual Work])/60,0)

John

• Edited by Thursday, January 9, 2020 1:33 AM plan B
• Marked as answer by Thursday, January 9, 2020 4:30 AM
Thursday, January 9, 2020 12:02 AM
• Thanks John

The following is what I am getting. Is there some setting for these number fields I need to set to show decimals on the calculation? It seems to be ignoring the decimal digits entirely

Thanks

IIf([Number5]<>0,[Number5]-[Actual Work]\60,0)

 Actual Work BOE Hrs (Number5) CSC Task Mgr Number10 11.7 h 24 James 13

 Actual Work BOE Hrs (Number5) CSC Task Mgr Number10 20.2 h 32 Bill 12 13.8 h 16 Pete 3

• Marked as answer by Thursday, January 9, 2020 4:30 AM
Wednesday, January 8, 2020 9:21 PM
• Thanks John

This works now.

I ended up actually highlighting Number5 in the formula and using the drop down in FIELD I selected the field Number5 replacing what was in the formula with the same value Number5. The difference is that I overlaid Number5 in the formula with the selection from the field drop down itself.

Kind of strange but that worked giving the decimal places.

IIf([Number5]<>0,[Number5]-[Actual Work]/60,0)

Thanks again

• Marked as answer by Thursday, January 9, 2020 4:30 AM
Thursday, January 9, 2020 4:29 AM

### All replies

• Hotmail1,

The "hrs" is simply something Project adds to the visual display, the actual work itself is just a number, but, like all time related values in Project, internally it is stored in minutes. The following formula should work in the first case

Number2=[Actual Work]/60-[Number5]

The second formula will be very similar

Number3=[Number5]*0.9-[Actual Work]/60

Hope this helps.

John

• Marked as answer by Thursday, January 9, 2020 4:31 AM
Wednesday, January 8, 2020 2:28 AM
• Thanks John

=IF(F2<>0,(SUM(F2-G2)))

What about the iif statement included with Number2=[Actual Work]/60-[Number5]

We check actual work and if it is not = 0 we do the formula

How would all of that look?

Wednesday, January 8, 2020 1:36 PM
• Hello

One other slight problem when I used the formula is that it seems to be subtracting at whole numbers and not taking into consideration tenths like when subtracting 2.1 - 2 it is 0 in the number field being used like number 2

Thoughts?

Thanks

Wednesday, January 8, 2020 2:14 PM
• Hotmail1,

I didn't know what to make of your "if" statement since it referred to Excel rows and columns with no explanation of what those were supposed to be in Project. And it's not clear how the "SUM" should be handled, or what the formula should produce if Number5 (G2?) is not zero?

I need a more complete definition of what you want.

With respect to the second issue of not seeming to handle decimal values, this is what the formula produces in my test case (If condition added as shown) John

• Marked as answer by Thursday, January 9, 2020 4:31 AM
Wednesday, January 8, 2020 4:58 PM
• Thanks John

The following is what I am getting. Is there some setting for these number fields I need to set to show decimals on the calculation? It seems to be ignoring the decimal digits entirely

Thanks

IIf([Number5]<>0,[Number5]-[Actual Work]\60,0)

 Actual Work BOE Hrs (Number5) CSC Task Mgr Number10 11.7 h 24 James 13

 Actual Work BOE Hrs (Number5) CSC Task Mgr Number10 20.2 h 32 Bill 12 13.8 h 16 Pete 3

• Marked as answer by Thursday, January 9, 2020 4:30 AM
Wednesday, January 8, 2020 9:21 PM
• Hotmail1,

That's interesting. No, there is no setting that should cause the rounding. What version of Project are you using again?

Try this, instead of using custom Number fields, try using a couple of text fields. In the screen shot below I use Text29 for the BOE hrs and Text30 for the formula

Text30 = IIf([Text29]<>0,[Text29]-[Actual Work]/60,0)

Does that work for you? If that doesn't work, here's another idea. Use this formula

Number10=IIf([Number5]<>0,([Number5]*60-[Actual Work])/60,0)

John

• Edited by Thursday, January 9, 2020 1:33 AM plan B
• Marked as answer by Thursday, January 9, 2020 4:30 AM
Thursday, January 9, 2020 12:02 AM
• Thanks John

This works now.

I ended up actually highlighting Number5 in the formula and using the drop down in FIELD I selected the field Number5 replacing what was in the formula with the same value Number5. The difference is that I overlaid Number5 in the formula with the selection from the field drop down itself.

Kind of strange but that worked giving the decimal places.

IIf([Number5]<>0,[Number5]-[Actual Work]/60,0)

Thanks again

• Marked as answer by Thursday, January 9, 2020 4:30 AM
Thursday, January 9, 2020 4:29 AM
• Hotmail1,

Yeah that is strange. Apparently there was some type of minor corruption with either the Number5 field or the formula itself. I'm glad you got it working.

Thanks for the feedback.

John

Thursday, January 9, 2020 3:29 PM