Creating a status column off multiple date fields

[jrkmorgan]

I need an efficient way to create a singular column for status based off multiple columns with dates. I'm looking for the last non null column and return a status based off of it. A simple sample table.....

I was trying to create the formula with !isNull but it isn't coming out how I need.

SPUD	Completion	Abandonment	First Prod	STATUS
1/1/2023	2/1/2023		5/1/2023	PRODUCING
1/15/2023	3/1/2023			COMPLETION
1/30/2023	2/15/2023	7/1/2023		ABANDONED

[usmanbashir]

@jrkmorgan 

You can also try !isEmpty() function. Depending on how your data is entered, they can be either null, empty, or whitespaces. When i copy and pasted your table in, those 'empty' values actually had whitespaces, so you could use data cleansing to remove whitespace. 

I then used below formula to output Status field correctly:

IF !isEmpty([First Prod]) THEN 'PRODUCING'
ELSEIF !IsEmpty([Abandonment]) THEN 'ABANDONED'
ELSEIF !IsEmpty([Completion]) THEN 'COMPLETION'
ELSE ''
ENDIF

Hope this helps!

[jrkmorgan]

I built the table based off the message creation table option - it wasn't the actual table I'm working with.

[usmanbashir]

@jrkmorgan - please see attached workflow. Let me know if this is what you were looking for!

[jrkmorgan]

If a certain column is null, it is the prior column's status I need. I.E. if the production date column is empty but completion is not, then the status would be completion. Basically for each row, the header of the column that has the last date.

[usmanbashir]

That's what I built into the workflow. Let me know if results from workflow are not what you're looking for.

The workflow attached accommodates for more than just those 3 fields. Since you said that was just sample data, I made the assumption you wanted it more dynamic rather than to create an IF ELSE statement with hardcode column names. The workflow prioritizes the right most column than moves left until a value is found. 

From there, you can adjust the workflow as needed.

Hope this helps!

[HomesickSurfer]

Hi @jrkmorgan 

Here goes...

IF 
!IsEmpty([First Prod]) AND !IsNull([First Prod]) THEN 'PRODUCING' ELSEIF 
IsEmpty([First Prod]) OR IsNull([First Prod]) AND !IsEmpty([Abandonment]) OR !IsNull([Abandonment]) THEN 'ABANDONED' 
ELSE 'COMPLETION' 
ENDIF