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

Workflow

Accepted answers

IraWatt

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

All comments

philipmannering

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

IraWatt

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

binu_acs

@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

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262

JORGE4900 235