IFS Statement issues

[iosiflupis]

Hello all,

I am working on a spreadsheet that uses the following columns: Scheduled Departure Time (HH:MM), Actual Departure Time (HH:MM) to calculate Departure Delay (Act - Sched). Using that calculated column I am trying to have a column that will tell me Early, Late, On-Time or Cancelled depending on the results in the Departure Delay column.

I have this IFS statement but cannot get it to recognize either a blank cell or a cell with Null in it to return Cancelled. =IFS([@[Departure Delay]]<0,"Early",[@[Departure Delay]]=0,"On-Time",[@[Departure Delay]]>0,"Late")
I have tried this as =IFS([@[Departure Delay]]<0,"Early",[@[Departure Delay]]=0,"On-Time",[@[Departure Delay]]>0,"Late",[@[Departure Delay]]=Null,"Cancelled")

I think that even if the cells are blank they are filled with an invisible 0. Some of the cells in the Departure Delay column are calculated to 0 and should show as On-Time.

What have I not seen?

Thank you all

 

[SanjayGMusafir]

Try this -

IFS need to end with a true Value

Thus either use this

=IFS([@[Departure Delay]]<0,"Early",[@[Departure Delay]]=0,"On-Time",[@[Departure Delay]]>0,"Late",[@[Departure Delay]]="","Cancelled",True,"")

or this

=IFS([@[Departure Delay]]<0,"Early",[@[Departure Delay]]=0,"On-Time",[@[Departure Delay]]>0,"Late",True,"Cancelled")

 

[Fluff]

Yppu need to check for a blank cell 1st

=IFS([@[Departure Delay]]="","Cancelled",[@[Departure Delay]]<0,"Early",[@[Departure Delay]]=0,"On-Time",[@[Departure Delay]]>0,"Late")

 

[SanjayGMusafir]

Yppu need to check for a blank cell 1st

=IFS([@[Departure Delay]]="","Cancelled",[@[Departure Delay]]<0,"Early",[@[Departure Delay]]=0,"On-Time",[@[Departure Delay]]>0,"Late")

Just for my understanding, does the above formula don't need a true value to complete IFS statement?

Thanks a lot

 

[Fluff]

No, you do not need that.

 

[iosiflupis]

Thank you both, I used the formula that checked the blank first. Evidently, you do not have to have a true statement at the end.

have a wonderful day!

 

[Fluff]

Glad we could help & thanks for the feedback.