Help with Date formula

[mmjones31]

I have 2 date columns I was trying to write an IF statement for;
actual (U)
plan (W)

IF(actual<plan,”early”)
IF(actual=plan, “on time”)
IF(plan>actual, “over due”)

One problem I’m having is the data set I’m using if a delivery hasn’t been created then the actual date is represented in the cell as 00/00/00, but other rows have a valid dates.

How can I combine the above to get these results?

 

[Toadstool]

Hi MMJones31,

Does this do what you want?

MMJones31.xlsx
	U	V	W	X	Y
1	Actual		Plan		Result
2	1/1/2022		1/1/2022		On time
3	1/1/2022		12/31/2021		Overdue
4	1/1/2022		2/2/2022		Early
5	1/1/2022
6			1/1/2022
Sheet1

Cell Formulas
Range	Formula
Y2:Y6	Y2	=IF((OR(U2<1,W2<1)),"",IF(W2-U2=0,"On time",IF(W2-U2<0,"Overdue","Early")))

 

[mmjones31]

Thanks. That formula is giving me a #value error in some rows where the cell in column U is 00/00/0000and is not working exactly right.

Example:
Actual (column U)
00/00/0000
2/2/2022
00/00/2022
2/1/2022
1/28/2021
12/1/2021

Plan (column W)
3/9/2022
2/1/2022
3/21/2022
2/2/2022
1/31/2021
11/29/2021

 

[Toadstool]

I'm guessing the non-date Actuals are text so see if this works.

MMJones31.xlsx
	U	V	W	X	Y
1	Actual		Plan		Result
2	00/00/0000		3/9/2022
3	2/2/2022		2/1/2022		Overdue
4	00/00/2022		3/21/2022
5	2/1/2022		2/2/2022		Early
6	1/28/2021		1/31/2021		Early
7	12/1/2021		11/29/2021		Overdue
2nd

Cell Formulas
Range	Formula
Y2:Y7	Y2	=IF(N(U2)<1,"",IF(W2-U2=0,"On time",IF(W2-U2<0,"Overdue","Early")))

 

[mmjones31]

I'm guessing the non-date Actuals are text so see if this works.

MMJones31.xlsx
	U	V	W	X	Y
1	Actual		Plan		Result
2	00/00/0000		3/9/2022
3	2/2/2022		2/1/2022		Overdue
4	00/00/2022		3/21/2022
5	2/1/2022		2/2/2022		Early
6	1/28/2021		1/31/2021		Early
7	12/1/2021		11/29/2021		Overdue
2nd

Cell Formulas
Range	Formula
Y2:Y7	Y2	=IF(N(U2)<1,"",IF(W2-U2=0,"On time",IF(W2-U2<0,"Overdue","Early")))

Yes, this works! Yes, unfortunately the non-date are text. Is there anyway for the non date to reflect maybe as “no delivery”?
I appreciate your help immensely- this really had me stumped.

 

[mmjones31]

Yes, this works! Yes, unfortunately the non-date are text. Is there anyway for the non date to reflect maybe as “no delivery”?
I appreciate your help immensely- this really had me stumped.

Sorry I figured that out!! It’s been a long day!!! Thank you so very much!!!!

 

[Toadstool]

You're welcome.
...but I'd appreciate you marking my post as the solution of you're OK with that.

 

[mmjones31]

You're welcome.
...but I'd appreciate you marking my post as the solution of you're OK with that.

Done. Sorry new member, I marked your post.