Formula does not work as anticpated

[MikeFrancis1959]

I have a formula called "test" that looks at date columns and it works except the last part where I think I am telling it if there is no date in column "2nd GI" "Plan 4/7" or "Plan 4/11" to use the [PO Due Date] since there is a date there but it comes back blank, what do I need to look for to correct this.

IF !IsEmpty([2nd GI]) 

THEN [2nd GI]

ELSEIF

IsEmpty([4/11 Plan])

THEN [4/7 Plan]

ELSE [PO Due Date]

ENDIF

 Due Date 1st GI 2nd GI 4/7 Plan 4/11 Plan test

3/31/2023

3/24/2023

3/30/2023

3/8/2023

3/14/2023

Thanks,

Mike

[PhilipMannering]

Should it be "!IsEmpty([4/11 Plan])" , (i.e. chuck in an exclamation mark at the front of your second clause)?

[IraWatt · Accepted solution]

Hey @MikeFrancis1959, think you are missing a ! on the isEmpty also not sure why 4/11 is not used. I would have thought the formula should be:

IF !IsEmpty([2nd GI]) THEN [2nd GI]
ELSEIF !IsEmpty([4/11 Plan]) THEN [4/11 Plan]
ELSEIF !IsEmpty([4/7 Plan]) THEN [4/7 Plan]
ELSE [PO Due Date]
ENDIF

All the best,

Ira

[cjaneczko]

It sounds like more is needed to the formula if you are checking that all 3 fields are empty.

IF !IsEmpty([2nd GI]) Then [2nd GI]
ElseIF !IsEmpty([4/11 Plan]) Then [4/11 Plan]
ElseIF !IsEmpty([4/7 Plan]) Then [4/7 Plan]
Else [PO Due Date]
EndIF

[MikeFrancis1959]

yes you are right

[MikeFrancis1959]

If I did not have the support of the members on this forum I would be lost! I have only been a user for about 3 months and I keep every formula in a file so I can reference back to the structure of it and also to take the time to break it down to understand the flow. 

[MikeFrancis1959]

I have one last question, instead of always looking at a column like my example of 4/7 PLAN or 4/11 PLAN, the title will change but it will always be in the same position of my excel file, what tool is the best to help identify it like field 36 & field 37 so I dont have to worry about the name change?

Mike

[binuacs]

@MikeFrancis1959 One option is to use the dynamic select tool - in which you can use the formula  ([FieldNumber] =36) OR FieldNumber] =37) for selecting the fields based on positions

[MikeFrancis1959]

I will try this tool!

Mike