Dispatch Formula Between Dates

[RD1982]

Hi Mr Excels Gurus - Could you please help with the below? This will cover the above message template which I think was a bit confused. Thanks.

Expected PGI Date	PGI Date	PGI Status
27/06/2023	27/06/2023	On Time
27/06/2023	27/06/2023	On Time
27/06/2023	0/01/1900	Late
27/06/2023	27/06/2023	On Time
27/06/2023	27/06/2023	On Time
27/06/2023	27/06/2023	On Time
27/06/2023	27/06/2023	On Time
27/06/2023	27/06/2023	On Time
27/06/2023	27/06/2023	On Time
27/06/2023	27/06/2023	On Time
27/06/2023	27/06/2023	On Time
27/06/2023	28/06/2023	Late
27/06/2023	28/06/2023	Late
27/06/2023	26/06/2023	Expedited
27/06/2023	26/06/2023	Expedited
27/06/2023	26/06/2023	Expedited
27/06/2023	26/06/2023	Expedited
3/07/2023	0/01/1900	On Time

 

[SanjayGMusafir]

Assuming columns are A, B and C

Try this in C2 and below -

=IFS(B2=0,"Late",B2>A2,"Expedited",True,"On Time")

 

[RD1982]

@SanjayGMusafir Thank you for your feedback. I haven't used IFS before great to know this one....But it seems not capturing the correct results when PGI date occurred 1D after expected PGI Date as this should be "Late". Also, when PGI Date is = 0 but Expected PGI Date = future (not past due) this should be tagged as "On Time".

What do you recommend to adjust the below?

 

[SanjayGMusafir]

But it seems not capturing the correct results when PGI date occurred 1D after expected PGI Date as this should be "Late". Also, when PGI Date is = 0 but Expected PGI Date = future (not past due) this should be tagged as "On Time".

Try this and revert...

=IFS(And(U2="0/01/1900",T2<Today()),"Late",T2>U2,"Expedited",True,"On Time")

 

[RD1982]

@SanjayGMusafir - Apologies I cannot find the reason of below is still not capturing the correct result. The dates are all formatted as date so why is not picking the 1st condition of formula you shared?

 

[SanjayGMusafir]

The dates are all formatted as date so why is not picking the 1st condition of formula you shared?

Ideally it should work with this too... Try again and update...

=IFS(And(U2=0,T2<Today()),"Late",T2>U2,"Expedited",True,"On Time")

 

[RD1982]

@SanjayGMusafir I don't know what else I can try and I thank you for trying to help but something is not working I cannot find where the problem is...

 

[SanjayGMusafir]

I cannot find where the problem is...

At times it can some effort as there can be some ambiguity in understanding the need or the formats...

Try this and revert...

=IFS(And(U2=0,T2<Today()),"Late",T2>U2,"Expedited",T2=U2,"On Time",True,"")

 

[RD1982]

@SanjayGMusafir - you're so patient and amazing in trying to help me....I couldn't thank you enough! Please find below. Could we amend the "late" to pick up the first blank line as "Late" ...we're almost there.

May I understand better the concept of "True" at the end?

 

[SanjayGMusafir]

Could we amend the "late" to pick up the first blank line as "Late" ...we're almost there

I couldn't understand the above. Can you explain it better.

May I understand better the concept of "True" at the end?

The purpose of True in this formula is if none of the predefined conditions are met then what should it reply. Here I have used a "" (Blank) but that can be amended.

The benefit of using IFS function is that it allows you to add or reduce conditions without worrying about the closing or opening brackets. All you need to remember is the order in which excel should process the conditions. IFS function stops at the first criteria that is met. so order is important.

Anyhow, if you can better explain the above first thing, I would try to help on that too.