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

 

[bebo021999]

Try in D2:
=WORKDAY(A2,C2)

if you have somewhere to store holidays. i.w, X1:X10:
=WORKDAY(A2,C2,$X$1:$X$10)

 

[RD1982]

Thank you @bebo021999 ... The above formula I tried for the orders with short lead time (i.e.: 1, 2, 3 days) but if I apply for orders like sea freight (36 days) which we count all days (including wkd) and/or rail which is 14 days also including weekends then I'd have the incorrect dates for these lanes....do you see a way I could differentiate them?

 

[Fluff]

Not really surprising it didn't work for Sea freight or Rail, as you never mentioned anything about that.

Can you post some sample data that shows all the relevant data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Phuoc]

Try this

=WORKDAY(ASN Creation Date + PDT-1,1,Holidays)

.

 

[RD1982]

amazing let me try this tool. Thanks! Will come back to you soon

 

[RD1982]

Dear

bebo021999, Phuoc and Fluff,​

I was thinking a better way to exemplify my query after your advise, I found the below.

I'm using the formula: =IF(ISBLANK(T3),"Not PGI Yet",IF(E3=T3,"Expedited",IF(NETWORKDAYS(E3,T3)<=2,"On Time","Late"))) - The problem is when I have PGI Date as 0/01/1900 and STO Delivery Date is past due...I am not capturing as late...to check. Could you please help me?

I attached excel file with some examples and my notes on column U to facilitate your side. Much appreciate.

STO

Line Item

Created on

Supp Plant

Rec Plant

STO Delivery Date

STO Qty

UOM

Trip ID

Job Id

OBD

ASN

ROUTE

PGI Date

Planned GR Date

GR Date

Status of STO Line?

Late PGId?

Late GR?

Overdue

Notes

7403480157

10

23/06/2023

1027

1026

7/07/2023

200.000

CAR

A1109902

7403480157

0087380233

0183735083

AU0996

26/06/2023​

7/07/2023​

0/01/1900

OPEN

On Time

Late

No Action Required

Order created on 23/06 so PGI = next work day whch in this case is on time and GR should be on time because is not due date yet (07/07/2023)

7403480927

10

26/06/2023

1026

1031

11/07/2023

18.000

KEG

A1110905

7403480927

0087384705

AU0994

0/01/1900​

11/07/2023​

0/01/1900​

OPEN

Delayed

On Time

No Action Required

PGI didn't happen on 27/07 as expected so it's late

7403481939

20

26/06/2023

1027

1030

29/06/2023

260.000

CAR

A1109408

7403481939

0087388310

0183745139

AU0260

28/06/2023​

29/06/2023​

29/06/2023​

COMPLETE

Late

Late

No Action Required

This should NOT be GR late because was received on 29/06 as expected due date

7403480298

10

23/06/2023

1030

1027

27/06/2023

271.000

CAR

0087395354

0183740692

AU0330

27/06/2023​

27/06/2023​

28/06/2023​

COMPLETE

Late

Late

Overdue

PGI should be on 26/06 and GR should be on 27/06 because we have 1 day lead time and therefore this is classified as overdue order

 

[RD1982]

Another quick one is how do I calculate the number of days late if my PGI date has the 0/01/1900 date?

For example below table I should have the number if "Days PGI Late" as showing below but if I calculate how many days we are late betwwen "Expected PGI Date and PGI Date" all I get is -45103....-388895

Expected PGI Date	PGI Date	Days PGI Late	If calculate the difference appear the below
26/06/2023	0/01/1900	3	-45103
27/06/2006	0/01/1900	2	-38895
28/06/2023	0/01/1900	1	-45105
28/06/2023	0/01/1900	1	-45105

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D
1	Expected PGI Date	PGI Date	Days PGI Late	If calculate the difference appear the below
2	26/06/2023	00/01/1900	3	3
3	27/06/2023	00/01/1900	2	2
4	28/06/2023	00/01/1900	1	1
5	28/06/2023	00/01/1900	1	1
Sheet4

Cell Formulas
Range	Formula
D2:D5	D2	=NETWORKDAYS(A2,IF(B2=0,TODAY(),B2))-1

 

[RD1982]

Hi @Fluff many thanks did work for calculation of PGI Days. Thank you very much! May I ask you (sorry i don't get) the meaning of repeating B2 and -1? so just I understand the concept of the calculation...I got it until the "today()". Thanks in advance.