none
Formula for subtracting two work hours fields. One has an h in it and the other does not RRS feed

  • 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

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