ETA Round Date Excluding Weekends

[RD1982]

Hi Gurus,

Could you please help to build a workday and/or other formula for the below table?

Formula Location: Correct STO ETA column which I calculate the ASN Creation Date + PDT by rounding up if falls on weekends
ETA Difference status = if Correct STO ETA column is equal to ASN Delivery Date Column

Problem: The column called "Correct STO ETA" will need to round date ONLY if falls on Saturday, Sunday and or Public Holidays. First line is an example of what should be showing but I get only 20/05 which is Saturday.

Thanks in advance!

ASN Creation Date	ASN Delivery Date	PDT	Correct STO ETA	ETA Difference
19/05/2023	22/05/2023	1	22/05/2023	0
19/05/2023	22/05/2023	1	20/05/2023	-2
19/05/2023	22/05/2023	1	20/05/2023	-2
19/05/2023	22/05/2023	1	20/05/2023	-2
19/05/2023	22/05/2023	1	20/05/2023	-2
19/05/2023	22/05/2023	1	20/05/2023	-2

 

[RD1982]

Hi @bebo021999 , @Fluff and @Phuoc - 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

 

[Fluff]

As this is now a totally different question, it needs a new thread. Thanks