Answered by:
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(F2G2)))
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
Answers

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 Hotmail1 Thursday, January 9, 2020 4:31 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 Hotmail1 Thursday, January 9, 2020 4:31 AM

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 John  Project Thursday, January 9, 2020 1:33 AM plan B
 Marked as answer by Hotmail1 Thursday, January 9, 2020 4:30 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 Hotmail1 Thursday, January 9, 2020 4:30 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 Hotmail1 Thursday, January 9, 2020 4:30 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 Hotmail1 Thursday, January 9, 2020 4:31 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 Hotmail1 Thursday, January 9, 2020 4:31 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 Hotmail1 Thursday, January 9, 2020 4:30 AM

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 John  Project Thursday, January 9, 2020 1:33 AM plan B
 Marked as answer by Hotmail1 Thursday, January 9, 2020 4:30 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 Hotmail1 Thursday, January 9, 2020 4:30 AM
