MS Project Custom Date and Duration Fields

Question

I'm having a problem writing the function to calculate the a custom number field based on two custom date fields.

I set up [date1] and [date2] for manual entry. Based on information I found on the forum I created a custom number field [number1]to calculate the duration from [date1] to [date2] If the [date1] and [date2] are populated with dates the formula works. However, if either [date1] and [date2] are NA (no data entered) the customer number field [number1] should be blank or 0 days.

My formula is:

IIf([Date1]="NA",0,IIf([Date2]="NA",ProjDateDiff([Date1],Now(),[Project Calendar])+1/480,ProjDateDiff([Date1],[Date2],[Project Calendar])+1/480))

Id both [date1] and [date2] are populated I get the correct duration as a number.

If both [date1] and [date2] are NA I get #ERROR and is should be blank or 0

If [date1] is populated and [date2] is NA I get #ERROR but is be a calculation from [date1] to Now()

If [date1] is NA and [date2] is populated I get #ERROR but it should be blank or 0

I think I read that you can't use Boolean functions in MS Project formulas specific OR.

I've been working on this off and on for two weeks and can't get anywhere with it.

I'd appreciate any help other SME's may be able to provide.

I'm using Microsoft® Project 2019 MSO (Version 2208 Build 16.0.15601.20644) 64-bit

Answer 1

Randy Storey,

Well my friend, your two weeks of frustration are at an end - I got you covered.

Both And and Or are fully usable in custom field formulas, whoever said they couldn't be used is incorrect. What is not directly readable is the "NA" value. Internally the "NA" value is a very large number so it must be treated as a number, not a string value. And the Now() function doesn't work because it will give you the difference up to the minute (i.e. right now) which I don't think is what you intend. Herewith is your solution.

IIf(([Date1]>50000 And [Date2]>50000) Or ([Date1]>50000 And isdate([Date2])),0,IIf(isdate([Date1]) And [Date2]>50000,projdatediff([Date1],[Current Date])/480+1,projdatediff([Date1],[Date2])/480+1))

Hope this helps.

John

Answer 2

Randy Storey,

You're welcome and thanks for the feedback.

John

Answer 3

Thank you so much. It worked perfectly.