Excel formula - Overdue orders with dates

[RD1982]

Hi Mr. Excels,

Could you please help me to build formula which will inform as "overdue" if an order passed through "Planned GR Date and GR Date" as exemplified below?
I tried to attached file to facilitate the check but it seems not available so I copied/past table. Thanks in advance.

The below informed Days GR late as 4,5 and 6 days = this is overdue but column J is not showing that.... I guess a new condition on column C is required?
Formulas I'm using in each column:

Late PGI Date => =IFS(AND(B39=0,A39<TODAY()),"Late",A39>B39,"Expedited",A39=B39,"On Time",TRUE,"Late")
Days PGI Late => =NETWORKDAYS(A39,IF(B39=0,TODAY(),B39))-1
Late GR => =IFS(D39>C39,"Late",D39=C39,"On Time",D39<C39,"Expedited",TRUE,"")
Days GR Late => =NETWORKDAYS(C39,IF(D39=0,TODAY(),D39))-1
Overdue => =IF(C39>=TODAY(),"No Action Required",IF(OR(D39="",D39>TODAY()),"Check",IF(C39>=D39,"Not Due","Overdue")))

Expected PGI Date	PGI Date	Planned GR Date	GR Date	Planned Release Date	Late PGId?	Days PGI Late	Late GR?	Days GR Late	Overdue
1/09/2023	31/08/2023	2/09/2023	0/01/1900	1/09/2023	Expedited	-3	Expedited	6	Not Due
1/09/2023	31/08/2023	2/09/2023	0/01/1900	1/09/2023	Expedited	-3	Expedited	6	Not Due
1/09/2023	31/08/2023	5/09/2023	0/01/1900	4/09/2023	Expedited	-3	Expedited	5	Not Due
1/09/2023	31/08/2023	5/09/2023	0/01/1900	4/09/2023	Expedited	-3	Expedited	5	Not Due
1/09/2023	31/08/2023	5/09/2023	0/01/1900	4/09/2023	Expedited	-3	Expedited	5	Not Due
1/09/2023	31/08/2023	5/09/2023	0/01/1900	4/09/2023	Expedited	-3	Expedited	5	Not Due
1/09/2023	31/08/2023	5/09/2023	0/01/1900	4/09/2023	Expedited	-3	Expedited	5	Not Due
1/09/2023	31/08/2023	5/09/2023	0/01/1900	4/09/2023	Expedited	-3	Expedited	5	Not Due
1/09/2023	31/08/2023	6/09/2023	0/01/1900	5/09/2023	Expedited	-3	Expedited	4	Not Due
1/09/2023	31/08/2023	6/09/2023	0/01/1900	5/09/2023	Expedited	-3	Expedited	4	Not Due
1/09/2023	31/08/2023	6/09/2023	0/01/1900	5/09/2023	Expedited	-3	Expedited	4	Not Due
1/09/2023	31/08/2023	6/09/2023	0/01/1900	5/09/2023	Expedited	-3	Expedited	4	Not Due
1/09/2023	31/08/2023	6/09/2023	0/01/1900	5/09/2023	Expedited	-3	Expedited	4	Not Due
1/09/2023	31/08/2023	6/09/2023	0/01/1900	5/09/2023	Expedited	-3	Expedited	4	Not Due
1/09/2023	0/01/1900	3/09/2023	0/01/1900	1/09/2023	Late	7	Expedited	6	Not Due
1/09/2023	0/01/1900	6/09/2023	0/01/1900	4/09/2023	Late	7	Expedited	4	Not Due
1/09/2023	0/01/1900	6/09/2023	0/01/1900	4/09/2023	Late	7	Expedited	4	Not Due
1/09/2023	0/01/1900	6/09/2023	0/01/1900	4/09/2023	Late	7	Expedited	4	Not Due
1/09/2023	0/01/1900	6/09/2023	0/01/1900	4/09/2023	Late	7	Expedited	4	Not Due
1/09/2023	0/01/1900	6/09/2023	0/01/1900	4/09/2023	Late	7	Expedited	4	Not Due
1/09/2023	0/01/1900	7/09/2023	0/01/1900	5/09/2023	Late	7	Expedited	3	Not Due
1/09/2023	0/01/1900	7/09/2023	0/01/1900	5/09/2023	Late	7	Expedited	3	Not Due
1/09/2023	1/09/2023	7/09/2023	0/01/1900	0/01/1900	On Time	0	Expedited	3	Not Due
4/09/2023	4/09/2023	7/09/2023	0/01/1900	4/09/2023	On Time	0	Expedited	3	Not Due
4/09/2023	4/09/2023	7/09/2023	0/01/1900	4/09/2023	On Time	0	Expedited	3	Not Due
4/09/2023	4/09/2023	7/09/2023	0/01/1900	4/09/2023	On Time	0	Expedited	3	Not Due

 

[RD1982]

Hi Mr. Excels,

Could you please help me to build formula which will inform as "overdue" if an order passed through "Planned GR Date and GR Date" as exemplified below?
I tried to attached file to facilitate the check but it seems not available so I copied/past table. Thanks in advance.

The below informed Days GR late as 4,5 and 6 days = this is overdue but column J is not showing that.... I guess a new condition on column C is required?
Formulas I'm using in each column:

Late PGI Date => =IFS(AND(B39=0,A39<TODAY()),"Late",A39>B39,"Expedited",A39=B39,"On Time",TRUE,"Late")
Days PGI Late => =NETWORKDAYS(A39,IF(B39=0,TODAY(),B39))-1
Late GR => =IFS(D39>C39,"Late",D39=C39,"On Time",D39<C39,"Expedited",TRUE,"")
Days GR Late => =NETWORKDAYS(C39,IF(D39=0,TODAY(),D39))-1
Overdue => =IF(C39>=TODAY(),"No Action Required",IF(OR(D39="",D39>TODAY()),"Check",IF(C39>=D39,"Not Due","Overdue")))

Formula Validation DIFOT.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Expected PGI Date	PGI Date	Planned GR Date	GR Date	Planned Release Date	Late PGId?	Days PGI Late	Late GR?	Days GR Late	Overdue
39	1/09/2023	31/08/2023	2/09/2023	0/01/1900	1/09/2023	Expedited	-3	Expedited	8	Not Due
40	1/09/2023	31/08/2023	2/09/2023	0/01/1900	1/09/2023	Expedited	-3	Expedited	8	Not Due
41	1/09/2023	31/08/2023	5/09/2023	0/01/1900	4/09/2023	Expedited	-3	Expedited	7	Not Due
42	1/09/2023	31/08/2023	5/09/2023	0/01/1900	4/09/2023	Expedited	-3	Expedited	7	Not Due
43	1/09/2023	31/08/2023	5/09/2023	0/01/1900	4/09/2023	Expedited	-3	Expedited	7	Not Due
44	1/09/2023	31/08/2023	5/09/2023	0/01/1900	4/09/2023	Expedited	-3	Expedited	7	Not Due
45	1/09/2023	31/08/2023	5/09/2023	0/01/1900	4/09/2023	Expedited	-3	Expedited	7	Not Due
46	1/09/2023	31/08/2023	5/09/2023	0/01/1900	4/09/2023	Expedited	-3	Expedited	7	Not Due
47	1/09/2023	31/08/2023	6/09/2023	0/01/1900	5/09/2023	Expedited	-3	Expedited	6	Not Due
48	1/09/2023	31/08/2023	6/09/2023	0/01/1900	5/09/2023	Expedited	-3	Expedited	6	Not Due
49	1/09/2023	31/08/2023	6/09/2023	0/01/1900	5/09/2023	Expedited	-3	Expedited	6	Not Due
50	1/09/2023	31/08/2023	6/09/2023	0/01/1900	5/09/2023	Expedited	-3	Expedited	6	Not Due
51	1/09/2023	31/08/2023	6/09/2023	0/01/1900	5/09/2023	Expedited	-3	Expedited	6	Not Due
52	1/09/2023	31/08/2023	6/09/2023	0/01/1900	5/09/2023	Expedited	-3	Expedited	6	Not Due
53	1/09/2023	0/01/1900	3/09/2023	0/01/1900	1/09/2023	Late	9	Expedited	8	Not Due
54	1/09/2023	0/01/1900	6/09/2023	0/01/1900	4/09/2023	Late	9	Expedited	6	Not Due
55	1/09/2023	0/01/1900	6/09/2023	0/01/1900	4/09/2023	Late	9	Expedited	6	Not Due
56	1/09/2023	0/01/1900	6/09/2023	0/01/1900	4/09/2023	Late	9	Expedited	6	Not Due
57	1/09/2023	0/01/1900	6/09/2023	0/01/1900	4/09/2023	Late	9	Expedited	6	Not Due
58	1/09/2023	0/01/1900	6/09/2023	0/01/1900	4/09/2023	Late	9	Expedited	6	Not Due
59	1/09/2023	0/01/1900	7/09/2023	0/01/1900	5/09/2023	Late	9	Expedited	5	Not Due
60	1/09/2023	0/01/1900	7/09/2023	0/01/1900	5/09/2023	Late	9	Expedited	5	Not Due
61	1/09/2023	1/09/2023	7/09/2023	0/01/1900	0/01/1900	On Time	0	Expedited	5	Not Due
62	4/09/2023	4/09/2023	7/09/2023	0/01/1900	4/09/2023	On Time	0	Expedited	5	Not Due
63	4/09/2023	4/09/2023	7/09/2023	0/01/1900	4/09/2023	On Time	0	Expedited	5	Not Due
64	4/09/2023	4/09/2023	7/09/2023	0/01/1900	4/09/2023	On Time	0	Expedited	5	Not Due
65	4/09/2023	4/09/2023	7/09/2023	0/01/1900	4/09/2023	On Time	0	Expedited	5	Not Due
66	4/09/2023	4/09/2023	7/09/2023	0/01/1900	4/09/2023	On Time	0	Expedited	5	Not Due
67	4/09/2023	4/09/2023	7/09/2023	0/01/1900	4/09/2023	On Time	0	Expedited	5	Not Due
68	4/09/2023	4/09/2023	7/09/2023	0/01/1900	4/09/2023	On Time	0	Expedited	5	Not Due
69	4/09/2023	4/09/2023	7/09/2023	0/01/1900	4/09/2023	On Time	0	Expedited	5	Not Due
70	4/09/2023	4/09/2023	7/09/2023	0/01/1900	4/09/2023	On Time	0	Expedited	5	Not Due
71	4/09/2023	4/09/2023	7/09/2023	0/01/1900	4/09/2023	On Time	0	Expedited	5	Not Due
72	4/09/2023	4/09/2023	7/09/2023	0/01/1900	4/09/2023	On Time	0	Expedited	5	Not Due
73	4/09/2023	4/09/2023	7/09/2023	0/01/1900	4/09/2023	On Time	0	Expedited	5	Not Due
74	4/09/2023	5/09/2023	6/09/2023	0/01/1900	4/09/2023	Late	1	Expedited	6	Not Due
75	4/09/2023	5/09/2023	6/09/2023	0/01/1900	4/09/2023	Late	1	Expedited	6	Not Due
Sheet1

Cell Formulas
Range	Formula
F39:F75	F39	=IFS(AND(B39=0,A39<TODAY()),"Late",A39>B39,"Expedited",A39=B39,"On Time",TRUE,"Late")
G39:G75,I39:I75	G39	=NETWORKDAYS(A39,IF(B39=0,TODAY(),B39))-1
H39:H75	H39	=IFS(D39>C39,"Late",D39=C39,"On Time",D39<C39,"Expedited",TRUE,"")
J39:J75	J39	=IF(C39>=TODAY(),"No Action Required",IF(OR(D39="",D39>TODAY()),"Check",IF(C39>=D39,"Not Due","Overdue")))

View attachment 98586

Expected PGI Date	PGI Date	Planned GR Date	GR Date	Planned Release Date	Late PGId?	Days PGI Late	Late GR?	Days GR Late	Overdue
1/09/2023	31/08/2023	2/09/2023	0/01/1900	1/09/2023	Expedited	-3	Expedited	6	Not Due
1/09/2023	31/08/2023	2/09/2023	0/01/1900	1/09/2023	Expedited	-3	Expedited	6	Not Due
1/09/2023	31/08/2023	5/09/2023	0/01/1900	4/09/2023	Expedited	-3	Expedited	5	Not Due
1/09/2023	31/08/2023	5/09/2023	0/01/1900	4/09/2023	Expedited	-3	Expedited	5	Not Due
1/09/2023	31/08/2023	5/09/2023	0/01/1900	4/09/2023	Expedited	-3	Expedited	5	Not Due
1/09/2023	31/08/2023	5/09/2023	0/01/1900	4/09/2023	Expedited	-3	Expedited	5	Not Due
1/09/2023	31/08/2023	5/09/2023	0/01/1900	4/09/2023	Expedited	-3	Expedited	5	Not Due
1/09/2023	31/08/2023	5/09/2023	0/01/1900	4/09/2023	Expedited	-3	Expedited	5	Not Due
1/09/2023	31/08/2023	6/09/2023	0/01/1900	5/09/2023	Expedited	-3	Expedited	4	Not Due
1/09/2023	31/08/2023	6/09/2023	0/01/1900	5/09/2023	Expedited	-3	Expedited	4	Not Due
1/09/2023	31/08/2023	6/09/2023	0/01/1900	5/09/2023	Expedited	-3	Expedited	4	Not Due
1/09/2023	31/08/2023	6/09/2023	0/01/1900	5/09/2023	Expedited	-3	Expedited	4	Not Due
1/09/2023	31/08/2023	6/09/2023	0/01/1900	5/09/2023	Expedited	-3	Expedited	4	Not Due
1/09/2023	31/08/2023	6/09/2023	0/01/1900	5/09/2023	Expedited	-3	Expedited	4	Not Due
1/09/2023	0/01/1900	3/09/2023	0/01/1900	1/09/2023	Late	7	Expedited	6	Not Due
1/09/2023	0/01/1900	6/09/2023	0/01/1900	4/09/2023	Late	7	Expedited	4	Not Due
1/09/2023	0/01/1900	6/09/2023	0/01/1900	4/09/2023	Late	7	Expedited	4	Not Due
1/09/2023	0/01/1900	6/09/2023	0/01/1900	4/09/2023	Late	7	Expedited	4	Not Due
1/09/2023	0/01/1900	6/09/2023	0/01/1900	4/09/2023	Late	7	Expedited	4	Not Due
1/09/2023	0/01/1900	6/09/2023	0/01/1900	4/09/2023	Late	7	Expedited	4	Not Due
1/09/2023	0/01/1900	7/09/2023	0/01/1900	5/09/2023	Late	7	Expedited	3	Not Due
1/09/2023	0/01/1900	7/09/2023	0/01/1900	5/09/2023	Late	7	Expedited	3	Not Due
1/09/2023	1/09/2023	7/09/2023	0/01/1900	0/01/1900	On Time	0	Expedited	3	Not Due
4/09/2023	4/09/2023	7/09/2023	0/01/1900	4/09/2023	On Time	0	Expedited	3	Not Due
4/09/2023	4/09/2023	7/09/2023	0/01/1900	4/09/2023	On Time	0	Expedited	3	Not Due
4/09/2023	4/09/2023	7/09/2023	0/01/1900	4/09/2023	On Time	0	Expedited	3	Not Due