Multiple comparisons to give concatenated status

[Anonymous]

Hi all, 

I have a report that uses various dates to list the status of things - in order to condense the report I'd like to review the three date fields and then provide an update that confirms status, and then lists the date (e.g. Due - "23/09/2021")

I have tried IF AND as well as Switch options, but both have encountered errors for Switch I get "Switch does not support comparing values of type true/false with values of text" and for IF AND I think I may be getting the formulas wrong... 

So my code is like this currently: 

Status =

IF (AND(TODAY()>[Due], [Due]>=[Requested]), CONCATENATE("Due - ", [Due]),

IF (AND(TODAY()>[Requested], [Requested]>=[Received]), CONCATENATE("Requested - ", [Requested]),

IF (TODAY()>[027 Received], "Received", "0" )

But not giving the right outcomes, to clarify what I am trying to do with the formula - first check if the date in the [Due] column is later than TODAY, and then check that DUE is either the same as or equal to REQUEST (meaning that the form has been requested) and if that's the case the status should be REQUESTED, not DUE. 

Hopefully that makes sense, essentially it's just a IF ELSE statement? 

[Anonymous]

Thanks! Yes, I kept on playing with it and switched things around so found a way to work this with nested IF ANDs (it's not pretty...) - but your solution works too 

[amitchandak · Accepted solution]

@Anonymous , please share some sample data

Try Like

Status =
Switch (True() ,
AND(TODAY()>[Due], [Due]>=[Requested]), CONCATENATE("Due - ", [Due]),
AND(TODAY()>[Requested], [Requested]>=[Received]), CONCATENATE("Requested - ", [Requested]),
TODAY()>[027 Received], "Received", "0"
)