Question on formulas

• Question

• Hi,

I am trying to create a formula for my new cost1 column as follows: If the start date of a task is in a certain date range the task cost is written in the cost1 column otherwise a zero is written in the cost1 column. Can you help?

cost1=

Iif(([start] >= 10/2/17 AND [start]<=9/30/18), cost,0)

Thanks,

Mary Ellen

Friday, July 21, 2017 7:16 PM

All replies

• Mary Ellen,

You could try something like this:

Cost1=IIf([Start]>="10/2/17" And [Start]<="9/30/18",[Cost],0)

Hope this helps

John

Friday, July 21, 2017 8:07 PM
• Thank you.  The following is the formula that worked and cost1 rolls up:

﻿cost1=IIf([start]>=DateValue("10/2/17") AND [start]<=DateValue("9/30/18"), [cost],0)

This generates another question.  I have to apply this formula each time I open the file. Otherwise I get #ERROR in the rollups.  Do you know why?

Tuesday, July 25, 2017 12:30 PM
• mesheedy,

What exactly do you mean by having to apply the formula each time you open the file? Once the Cost1 field is customized with the formula and the file saved, that formula will be active until you remove it.

Did you set the option to rollup the sum for "calculation at group and summary rows"?

Do you have calculation set to automatic (File > Options > Schedule group > calculation option)?

John

Tuesday, July 25, 2017 3:21 PM
• The rollup option is set for "sum" .

Calculate project after each edit is set to "yes"  (﻿File > Options > Schedule > Calculation)﻿﻿﻿

I did save the file and then when reopening it I get the error.  Once I hit F9 all is well again.

Thanks Again.

﻿.

Tuesday, July 25, 2017 5:21 PM
• mesheedy,

What version of Project are you using? Is it updated with the latest cumulative update?

When you re-open the file and get the #ERROR on the Cost1 field of the summary line, do not hit F9 but go immediately to the options. Is calculation still "on"?

John

Tuesday, July 25, 2017 5:33 PM
• I am using MS Project 13 Professional.  Also the calculation option is still "on" after reopening the file.

Thanks,

Mary Ellen

Wednesday, July 26, 2017 12:14 PM
• Mary Ellen,

Is your Project 2013 fully updated? You should be at 15.0.4945.1000. If not, run Windows Update.

If you're fully updated and are still seeing the #ERROR on summary lines can you send me the file? If so, I can give you an e-mail address.

John

Wednesday, July 26, 2017 12:58 PM