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

 

[RD1982]

@SanjayGMusafir you are very clever...my formula skills is outdated I need some good classes to revamp all I lost along these years....I'm not good to study by myself... do you also master dashboard/power bi? I am looking for someone (will pay for some classes).

Thank you VERY much for clarifying the concept ....I get IFS inf ull now.

In this case when I mentioned to amend the "late" status I was thinking if we could somehow replace "today() with PGI Date to identify that if PGI is bigger than Expected PGI = Late... I tried but is not giving back the expected result as late

 

[RD1982]

@SanjayGMusafir - I tried to add the below yellow condition but it seems the formula simply ignores the fact that if PGI date is bigger than expected PGI date = late. Damned!

 

[SanjayGMusafir]

In this case when I mentioned to amend the "late" status I was thinking if we could somehow replace "today() with PGI Date to identify that if PGI is bigger than Expected PGI = Late... I tried but is not giving back the expected result as late

Now it's getting complicated than what you required earlier.

Please tell all criteria clearly in typing so that I can reset the formula accordingly.

 

[RD1982]

@SanjayGMusafir The formula you provided is awesome and it's capturing correctly for all conditions - except when PGI Date is bigger than Expected PGI Date which in this case we need to tag as "Late" (yellow highlighted below). Below is the picture including the formula you shared.

 

[SanjayGMusafir]

except when PGI Date is bigger than Expected PGI Date which in this case we need to tag as "Late"

Try this, this shall work as needed -

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

 

[RD1982]

OMG you nailed! I am re-reading your IFS explanation with more time today...yesterday I scanned to be honest, also checked online more details and ONLY today I got the concept. Silly me!
The IF statement replaces that multiple nestled condition that sometimes get stuck and give us errors....or say " too many arguments"....
I liked the below concept explained through link -> IFS function - Microsoft Support

=IFS([Something is True1, Value if True1,Something is True2,Value if True2,Something is True3,Value if True3)

@SanjayGMusafir THANK YOUUUUUUUUUUUUUUUUUUUUUUUUUU for SO MUCH patience with me!!!! It seems sometimes I get stuck....and blind!

 

[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.

 

[RD1982]

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.

sure will do I even saved this one on Notes...I'll never forget! Thank you very very much!!!