Excel Formula with Dates Help Needed

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

 

[SanjayGMusafir]

Is this what you are looking for -

Book2
	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	45170	45169	45171	0	45170	Expedited	-3	Expedited	8	Overdue
40	45170	45169	45171	0	45170	Expedited	-3	Expedited	8	Overdue
41	45170	45169	45174	0	45173	Expedited	-3	Expedited	7	Overdue
42	45170	45169	45174	0	45173	Expedited	-3	Expedited	7	Overdue
43	45170	45169	45174	0	45173	Expedited	-3	Expedited	7	Overdue
44	45170	45169	45174	0	45173	Expedited	-3	Expedited	7	Overdue
45	45170	45169	45174	0	45173	Expedited	-3	Expedited	7	Overdue
46	45170	45169	45174	0	45173	Expedited	-3	Expedited	7	Overdue
47	45170	45169	45175	0	45174	Expedited	-3	Expedited	6	Overdue
48	45170	45169	45175	0	45174	Expedited	-3	Expedited	6	Overdue
49	45170	45169	45175	0	45174	Expedited	-3	Expedited	6	Overdue
50	45170	45169	45175	0	45174	Expedited	-3	Expedited	6	Overdue
51	45170	45169	45175	0	45174	Expedited	-3	Expedited	6	Overdue
52	45170	45169	45175	0	45174	Expedited	-3	Expedited	6	Overdue
53	45170	0	45172	0	45170	Late	9	Expedited	8	Overdue
54	45170	0	45175	0	45173	Late	9	Expedited	6	Overdue
55	45170	0	45175	0	45173	Late	9	Expedited	6	Overdue
56	45170	0	45175	0	45173	Late	9	Expedited	6	Overdue
57	45170	0	45175	0	45173	Late	9	Expedited	6	Overdue
58	45170	0	45175	0	45173	Late	9	Expedited	6	Overdue
59	45170	0	45176	0	45174	Late	9	Expedited	5	Overdue
60	45170	0	45176	0	45174	Late	9	Expedited	5	Overdue
61	45170	45170	45176	0	0	On Time	0	Expedited	5	Overdue
62	45173	45173	45176	0	45173	On Time	0	Expedited	5	Overdue
63	45173	45173	45176	0	45173	On Time	0	Expedited	5	Overdue
64	45173	45173	45176	0	45173	On Time	0	Expedited	5	Overdue
65	45173	45173	45176	0	45173	On Time	0	Expedited	5	Overdue
66	45173	45173	45176	0	45173	On Time	0	Expedited	5	Overdue
67	45173	45173	45176	0	45173	On Time	0	Expedited	5	Overdue
68	45173	45173	45176	0	45173	On Time	0	Expedited	5	Overdue
69	45173	45173	45176	0	45173	On Time	0	Expedited	5	Overdue
70	45173	45173	45176	0	45173	On Time	0	Expedited	5	Overdue
71	45173	45173	45176	0	45173	On Time	0	Expedited	5	Overdue
72	45173	45173	45176	0	45173	On Time	0	Expedited	5	Overdue
73	45173	45173	45176	0	45173	On Time	0	Expedited	5	Overdue
74	45173	45174	45175	0	45173	Late	1	Expedited	6	Overdue
75	45173	45174	45175	0	45173	Late	1	Expedited	6	Overdue
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	=IFS(C39>=TODAY(),"No Action Required",OR(D39="",D39>TODAY()),"Check",AND(D39<>0,C39>=D39),"Not Due",TRUE,"Overdue")

 

[RD1982]

Is this what you are looking for -

Book2
	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	45170	45169	45171	0	45170	Expedited	-3	Expedited	8	Overdue
40	45170	45169	45171	0	45170	Expedited	-3	Expedited	8	Overdue
41	45170	45169	45174	0	45173	Expedited	-3	Expedited	7	Overdue
42	45170	45169	45174	0	45173	Expedited	-3	Expedited	7	Overdue
43	45170	45169	45174	0	45173	Expedited	-3	Expedited	7	Overdue
44	45170	45169	45174	0	45173	Expedited	-3	Expedited	7	Overdue
45	45170	45169	45174	0	45173	Expedited	-3	Expedited	7	Overdue
46	45170	45169	45174	0	45173	Expedited	-3	Expedited	7	Overdue
47	45170	45169	45175	0	45174	Expedited	-3	Expedited	6	Overdue
48	45170	45169	45175	0	45174	Expedited	-3	Expedited	6	Overdue
49	45170	45169	45175	0	45174	Expedited	-3	Expedited	6	Overdue
50	45170	45169	45175	0	45174	Expedited	-3	Expedited	6	Overdue
51	45170	45169	45175	0	45174	Expedited	-3	Expedited	6	Overdue
52	45170	45169	45175	0	45174	Expedited	-3	Expedited	6	Overdue
53	45170	0	45172	0	45170	Late	9	Expedited	8	Overdue
54	45170	0	45175	0	45173	Late	9	Expedited	6	Overdue
55	45170	0	45175	0	45173	Late	9	Expedited	6	Overdue
56	45170	0	45175	0	45173	Late	9	Expedited	6	Overdue
57	45170	0	45175	0	45173	Late	9	Expedited	6	Overdue
58	45170	0	45175	0	45173	Late	9	Expedited	6	Overdue
59	45170	0	45176	0	45174	Late	9	Expedited	5	Overdue
60	45170	0	45176	0	45174	Late	9	Expedited	5	Overdue
61	45170	45170	45176	0	0	On Time	0	Expedited	5	Overdue
62	45173	45173	45176	0	45173	On Time	0	Expedited	5	Overdue
63	45173	45173	45176	0	45173	On Time	0	Expedited	5	Overdue
64	45173	45173	45176	0	45173	On Time	0	Expedited	5	Overdue
65	45173	45173	45176	0	45173	On Time	0	Expedited	5	Overdue
66	45173	45173	45176	0	45173	On Time	0	Expedited	5	Overdue
67	45173	45173	45176	0	45173	On Time	0	Expedited	5	Overdue
68	45173	45173	45176	0	45173	On Time	0	Expedited	5	Overdue
69	45173	45173	45176	0	45173	On Time	0	Expedited	5	Overdue
70	45173	45173	45176	0	45173	On Time	0	Expedited	5	Overdue
71	45173	45173	45176	0	45173	On Time	0	Expedited	5	Overdue
72	45173	45173	45176	0	45173	On Time	0	Expedited	5	Overdue
73	45173	45173	45176	0	45173	On Time	0	Expedited	5	Overdue
74	45173	45174	45175	0	45173	Late	1	Expedited	6	Overdue
75	45173	45174	45175	0	45173	Late	1	Expedited	6	Overdue
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	=IFS(C39>=TODAY(),"No Action Required",OR(D39="",D39>TODAY()),"Check",AND(D39<>0,C39>=D39),"Not Due",TRUE,"Overdue")

 

[RD1982]

AHHH so good hear back from you. I changed the sign < (before was >) prior check (tested the "check" was not picking in the prior formula) below ... and wish to understand better the concept of "D <>0".

 

[SanjayGMusafir]

wish to understand better the concept of "D <>0"

Need to do that because with 0 in Column D, C would always be greater than D and test will stop there itself thus resulting in "Not Due"

 

[RD1982]

@SanjayGMusafir I actually revised again....I'm confused with the condition.... below example I typed 25/09/2023 in column D shouldn't this be a check if is >Today?

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
2	13/09/2023	13/09/2023	14/09/2023	15/09/2023	13/09/2023	On Time	0	Late	1	Overdue
3	13/09/2023	13/09/2023	14/09/2023	14/09/2023	13/09/2023	On Time	0	On Time	0	Not Due
4	13/09/2023	13/09/2023	15/09/2023	0/01/1900	13/09/2023	On Time	0	Expedited	0	No Action Required
5	13/09/2023	13/09/2023	11/09/2023	0/01/1900	13/09/2023	On Time	0	Expedited	4	Overdue
6	13/09/2023	13/09/2023	15/09/2023	0/01/1900	13/09/2023	On Time	0	Expedited	0	No Action Required
7	13/09/2023	13/09/2023	11/09/2023	14/09/2023	13/09/2023	On Time	0	Late	3	Overdue
8	13/09/2023	13/09/2023	15/09/2023	0/01/1900	13/09/2023	On Time	0	Expedited	0	No Action Required
9	14/09/2023	14/09/2023	12/09/2023	15/09/2023	14/09/2023	On Time	0	Late	3	Overdue
10	14/09/2023	14/09/2023	12/09/2023	15/09/2023	14/09/2023	On Time	0	Late	3	Overdue
11	14/09/2023	14/09/2023	15/09/2023	0/01/1900	14/09/2023	On Time	0	Expedited	0	No Action Required
12	14/09/2023	14/09/2023	15/09/2023	0/01/1900	14/09/2023	On Time	0	Expedited	0	No Action Required
13	14/09/2023	14/09/2023	15/09/2023	25/09/2023	14/09/2023	On Time	0	Late	6	No Action Required
14	14/09/2023	14/09/2023	15/09/2023	0/01/1900	14/09/2023	On Time	0	Expedited	0	No Action Required
15	14/09/2023	14/09/2023	21/09/2023	0/01/1900	14/09/2023	On Time	0	Expedited	-6	No Action Required
16	14/09/2023	14/09/2023	25/09/2023	0/01/1900	14/09/2023	On Time	0	Expedited	-8	No Action Required
17	14/09/2023	14/09/2023	15/09/2023	0/01/1900	0/01/1900	On Time	0	Expedited	0	No Action Required
Sheet1

Cell Formulas
Range	Formula
F2:F17	F2	=IFS(AND(B2=0,A2<TODAY()),"Late",A2>B2,"Expedited",A2=B2,"On Time",TRUE,"Late")
G2:G17,I2:I17	G2	=NETWORKDAYS(A2,IF(B2=0,TODAY(),B2))-1
H2:H17	H2	=IFS(D2>C2,"Late",D2=C2,"On Time",D2<C2,"Expedited",TRUE,"")
J2:J17	J2	=IFS(C2>=TODAY(),"No Action Required",OR(D2="",D2>TODAY()),"Check",AND(D2<>0,C2>=D2),"Not Due",TRUE,"Overdue")

 

[RD1982]

Need to do that because with 0 in Column D, C would always be greater than D and test will stop there itself thus resulting in "Not Due"

Thank for clarifying that...

 

[SanjayGMusafir]

I'm confused with the condition.... below example I typed 25/09/2023 in column D shouldn't this be a check if is >Today?

Excel checks in order of sequence

Since c13>Today() it stops there and returns "No Action Required"

 

[RD1982]

Excel checks in order of sequence

Since c13>Today() it stops there and returns "No Action Required"

@SanjayGMusafir is correct to state that in this case the "check" will basically not happen? Because if column C is below today = overdue straight away and if bigger than today would return "no actin required"

 

[SanjayGMusafir]

is correct to state that in this case the "check" will basically not happen?

I would suggest to TEST for various conditions by changing different values as per your requirements and see if it is working or not...