Help with my excel sheet

[victoriar]

Hello I need some assistance with some formulas in order for my project to work.
Some context, we have a 6 month period where we need to get tasks complete. In this example I have down 5/1/2024-12/1/2024
I would like the projected completion date correspond correctly to the due this month and status note.

As you can see now, I have 6/1/2024 - currently it says past due. However, under "status" I have selected done. I would like the "due this month" to say "not due"- and under the status note I would like it for it to say okay.
For past dates I do not want it to say past due.
For 7/1/2024 - I have not selected nothing under "status" and there for it says past due- which is perfect! but the status note I would like it to say Past due

For future dates "due this month" its showing "due this month" which Is great!

Can one help me get all this to work?

Authorization Timeline. Panda.xlsx
	B	C	D	E	F	G
24	Task	Projected completion date	Status	Due this month	Status note	Notes
25	Update Central Reach with new lessons			Not due this month	OK
26	Set up Teacher Training with new goals	06/01/2024	Done	Past due	OK
27	Discuss parents’ goal priorities			Not due this month	OK
28	New goals on Lesson Progress Sheet	07/01/2024		Past due	OK
29	Complete SrTx monthly tracking sheet on PIPS			Not due this month	OK
30	Team meeting			Not due this month	OK
31		7/16/2024		Due this month	OK
32				Not due this month	OK
33					OK
34	Parent training				OK
35					Past due
36					Past due
37					Past due
38					Past due
Prototype

Cell Formulas
Range	Formula
E25:E32	E25	=IF(OR(C25="",C25>EOMONTH(TODAY(),0)),"Not due this month",IF(C25<TODAY(),"Past due","Due this month"))
F25:F34	F25	=IF(D25="","OK",IF(D25="Done","OK",IF(D25="In progress","In progress",IF(D25="N/A","OK"))))
F35:F38	F35	=IF(AND(ISBLANK(D35),I35=$I$6),"Past due",IF(AND(I35=$I$6,D35=$D$2),"Past due","OK"))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F25:F50,F52:F70,F72:F107,F109:F128,F130:F165,F387:F412,F414:F432,F434:F2441	Cell Value	="Past due"	text	NO
F25:F50,F52:F70,F72:F107,F109:F128,F130:F165,F387:F412,F414:F432,F434:F2441	Cell Value	="OK"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
D25:D32	List	Done,In Progress,N/A
D33:D34	List	=#REF!
D35:D36	List	Done,In Progress,N/A
D37:D50	List	=#REF!

 

[Sufiyan97]

Hi,

Can you answer below questions?

As you can see now, I have 6/1/2024 - currently it says past due. However, under "status" I have selected done.

Do you want to change anything in this?

I would like the "due this month" to say "not due"- and under the status note I would like it for it to say okay.

You just want to change note from "due this month" to say "not due", correct?

For past dates I do not want it to say past due.

What you want in that case?

For future dates "due this month" its showing "due this month" which Is great!

What if the due date is not this month and is in upcoming month?

 

[victoriar]

Hello

Sufiyan97​

Of course I can.
So whenever I put a date, and I put done. Under "due this month" I would like it to say "not due this month" and under status "Okay".
For past due dates: That have already passed not due this month.
What if the due date is not this month and is in upcoming month?

As you can see now, I have 6/1/2024 - currently it says past due. However, under "status" I have selected done.

Do you want to change anything in this?
Under due this month if I have completed the task from previous months, I would like it to day under "due this month" to say not due this month and under "status "okay"

I would like the "due this month" to say "not due"- and under the status note I would like it for it to say okay.

You just want to change note from "due this month" to say "not due", correct?
Correct for past dates. That have already occured. and for the status to say "Okay"

For past dates I do not want it to say past due.

What you want in that case?
"Not due this month"

For future dates "due this month" its showing "due this month" which Is great!

What if the due date is not this month and is in upcoming month?

It should reflect the date we put

Like if we are in July and the next month is August but no dates have been entered it should say not due this month.


I have attached a picture of how everything should be in different scenarios. The orange highlighted cells