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]

Glad to help you. Thanks for your patience and persistence.

It can happen with anyone of us on a day that things are just before us and we might tend to ignore. Best part is that you got the essence/concept behind IFS function, it shall help you in more than many ways in future.

 

[SanjayGMusafir]

@RD1982 May I further recommend you to mark the solution thread as it helps others to reach the solution in a much faster way.

 

[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,"")