Today(), and due dates

[sss071]

Hi, I am a novice and am looking for a formula to do the following:

1. If due date has not arrived, show Not Due
2. If due date has arrived and is less than 30 days over, show Due
3. If due date has arrived and is 30 days or more over, show Overdue
4. If completion date has been entered into another cell, show Complete.

Due date = cell K4
Completion date = cell E4

 

[Phuoc]

4. If completion date has been entered into another cell, show Complete.

What is the address of the "another cell"?

 

[sss071]

What is the address of the "another cell"?

Hi,

The completion date will be entered in E4

 

[Phuoc]

Yes, try this

Book1
	E						K	L
3	Completion date						Due date
4							7/7/2022	Due
5	7/8/2022						7/8/2022	Complete
Sheet1

Cell Formulas
Range	Formula
L4:L5	L4	=IF(E4<>"","Complete",LOOKUP(TODAY()-K4,{-100000,0,31},{"Not Due","Due","Overdue"}))

 

[sss071]

=IF(E4<>"","Complete",LOOKUP(TODAY()-K4,{-100000,0,31},{"Not Due","Due","Overdue"}))

It works!! You're amazing. Thank you so much!