Automatic Status update depending on data entered

[Rusty Benson]

Hi
I have 3 columns (Columns A-G have data entered when the row is being used)
I =Due Date
H= Completed Date
L Status which has 3 choices: - Overdue, Completed, In Progress
Cell T1 has Today() entered to get a date to trigger the overdue reference.

Requirements
If Both I & H columns are empty return In Progress in column J (when there is data in the prior columns on the same row)
If Column I Due date has passed the show Overdue in column J
if column H has a completion date entered then show complete In Column J

I have tried using a nested if statement however it doesn't work properly.
IF(I5>0,"complete",IF(H5<$T$1,"overdue","in Progress"))

 

[dreid1011]

Perhaps this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Data							Completed	Due Date	Status										2/8/2024
2	data	data	data	data	data	data	data			In Progress
3	data	data	data	data	data	data	data	2/8/2024	2/9/2024	Complete
4	data	data	data	data	data	data	data		2/5/2024	Overdue
5	data	data	data	data	data	data	data		2/10/2024
6
Sheet1

Cell Formulas
Range	Formula
T1	T1	=TODAY()
J2:J6	J2	=IF(AND(COUNTA(A2:G2)>0,H2="",I2=""),"In Progress",IF(AND(COUNTA(A2:G2)>0,H2="",I2<$T$1),"Overdue",IF(H2>0,"Complete","")))

And what is the desired result for row 5? Currently it returns blank... should it also show "in progress" if there is a date in col I?

 

[Rusty Benson]

Thank you for that and Yes if there is data in the prior columns in row 5 it should show "in progress"

 

[dreid1011]

Thank you for that and Yes if there is data in the prior columns in row 5 it should show "in progress"

Okay, how about this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Data							Completed	Due Date	Status										2/8/2024
2	data	data	data	data	data	data	data			In Progress
3	data	data	data	data	data	data	data	2/8/2024	2/9/2024	Complete
4	data	data	data	data	data	data	data		2/5/2024	Overdue
5	data	data	data	data	data	data	data		2/10/2024	In Progress
6
Sheet1

Cell Formulas
Range	Formula
T1	T1	=TODAY()
J2:J6	J2	=IF(AND(COUNTA(A2:G2)>0,H2="",OR(I2="",I2>=$T$1)),"In Progress",IF(AND(COUNTA(A2:G2)>0,H2="",I2<$T$1),"Overdue",IF(H2>0,"Complete","")))

 

[Rusty Benson]

Again thank you.
In the same sheet I am trying to put a conditional format into the Due Date Column so that if the prior columns have data in the same row but no due date has been entered then that due date cell will say "Due Date Must Be Entered"

 

[dreid1011]

Again thank you.
In the same sheet I am trying to put a conditional format into the Due Date Column so that if the prior columns have data in the same row but no due date has been entered then that due date cell will say "Due Date Must Be Entered"

You won't be able to do that with a formula or conditional formatting if you want it to happen in that column. You will need to use VBA to achieve that. Cells can only hold a formula or a value, not both, and conditional formatting cannot change the value in a cell.