IF due date cell is < today = "" unless completed cell contains a date

[Paul1895]

Hi!

First time posting so i apologize in advance if i don't explain the issue i am having as well i should.

I have been asked to create a spreadsheet with gantt chart for the purpose of monitoring the status of a project. I've been told to keep it simple with the inclusion of a few visual aids to easily identify the tasks that are in progress, overdue and completed.

As my Excel knowledge is quite limited when it comes to formulas i was hoping someone could help me out with the following:

START (D9)	DUE (E9)	COMPLETED (F9)	STATUS (G9)
1-1-2019	3-1-2019		=IF(E10<TODAY(), "Overdue", "In Progress")

<tbody>
</tbody>

Looking to add to the formula in G10 so that it shows the text "Completed" if there is a date in F10.

Thanks in advance. Any assistance is greatly appreciated.

Paul

 

[Paul1895]

Thanks Claire. Appreciate the response. Ive had a little fiddle around with it because i was told by the Project Manager that the "In Progress" status should only be present when there is a due date and should read "-" if there is no due date.

Here is what i have now:

	(A)	(B)	(C)	(D)	(E)	(F)	(G)
(1)	ACTION	LEAD	START	DUE	END	%	STATUS
(2)	Write up project plan.	-	-	-	-	0%	-

<tbody>
</tbody>

G2 =IF(ISNUMBER(E2),"Completed",IF(AND(D2 < TODAY(),D2 > 0),"Past Due",IF(C2="-","-","In Progress")))


Can someone please tell me how i can add to the above formula so that the STATUS (G2) shows up as "Almost Due" if the DUE date (D2) is < Today but > 10 days prior to Today. e.g.

	(A)	(B)	(C)	(D)	(E)	(F)	(G)
(1)	ACTION	LEAD	START	DUE	END	%	STATUS
(2)	Write up project plan.	-	-	23-5-19	-	0%	Almost Due

<tbody>
</tbody>

(Today is 29-5-2019 in this example)

Hope this makes sense.

Thanks in advance for any assistance people can provide

Paul

 

[Paul1895]

ERROR MADE IN PREVIOUS POST - PLEASE REFER TO THIS ONE INSTEAD


Here is what i have now:

	(A)	(B)	(C)	(D)	(E)	(F)	(G)
(1)	ACTION	LEAD	START	DUE	END	%	STATUS
(2)	Write up project plan.	-	-	-	-	0%	-

<tbody>
</tbody>

G2 =IF(ISNUMBER(E2),"Completed",IF(AND(D2 < TODAY(),D2 > 0),"Past Due",IF(C2="-","-","In Progress")))


Can someone please tell me how i can add to the above formula so that the STATUS (G2) shows up as "Almost Due" if the DUE date (D2) is > Today by 10 days or less?

	(A)	(B)	(C)	(D)	(E)	(F)	(G)
(1)	ACTION	LEAD	START	DUE	END	%	STATUS
(2)	Write up project plan.	-	-	5-6-19	-	0%	Almost Due

<tbody>
</tbody>

(Today is 29-5-2019 in this example)

Hope this makes sense.

Thanks in advance for any assistance people can provide

Paul

 

[FormR]

Hi, does this cover all the scenarios?

Excel 2013/2016
	A	B	C	D	E	F	G
1	ACTION	LEAD	START	DUE	END	%	STATUS
2	Write up project plan.	-	-	05/06/2019	-		Nearly Due
3				28/05/2019			Past Due
4					04/05/2018		Completed
5							-
Sheet1

Cell Formulas
Range	Formula
G2	=IF(ISNUMBER(E2),"Completed",IF(ISNUMBER(D2),IF(D2),"Past Due",IF(D2>TODAY()-10,"Nearly Due","In Progress")),"-"))

 

[Paul1895]

Hi FormR,

Thank you for sending this through, it gives me 99% percent of what i was hoping to achieve. The formula you have provided has the STATUS cells as "Nearly Due" if the DUE date cells are any date > than today.

Are you able to make it so that the STATUS cells only say "Nearly Due" if the DUE date cells are between 1 and 10 days > Today but remain as "In Progress" if the DUE date cells are 11 or more days > Today? Please see row 6-9 bellow:

(Today is 30/05/2019 in this scenario)

Excel 2013/2016

	A	B	C	D	E	F	G
1	ACTION	LEAD	START	DUE	END	%	STATUS
2	Write up project plan.	-	-	05/06/2019	-		Nearly Due
3				28/05/2019			Past Due
4					04/05/2018		Completed
5							-

<tbody>
</tbody>

6

-

-

-

10/07/2019

-

-

In Progress

<tbody>
</tbody>

7

-

-

-

12/06/2019

-

-

In Progress

<tbody>
</tbody>

8

-

-

-

08/06/2019

-

-

Nearly Due

<tbody>
</tbody>

9

-

-

-

31/05/2019

-

-

Nearly Due

<tbody>
</tbody>

Thanks again for all you help with this.

​

G =IF(<font color="Blue">ISNUMBER(E2),"Completed",IF(ISNUMBER(D2),IF(D2<today(),"past due",if(D2 > TODAY()-10,"Nearly Due","In Progress"</today(),"past>)),"-"))

 

[ClaireS]

Here you go

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

Book1
	A	B	C	D	E	F	G
1	ACTION	LEAD	START	DUE	END	%	STATUS
2	write pp	-	-	05/06/2019	-		Nearly Due
3				28/06/2019			In Progress
4					04/05/2018		Completed
5				10/07/2019			In Progress
6				12/05/2019			Past Due
7				08/06/2019			Nearly Due
8				31/05/2019			Nearly Due
Sheet1

Cell Formulas
Range	Formula
G2	=IF(ISNUMBER(E2),"Completed",IF(ISNUMBER(D2),IF(D2),"Past Due",IF(AND(D2>=TODAY(),D2<=TODAY()+10),"Nearly Due","In Progress")),"-"))

[/FONT]

 

[Paul1895]

Perfect!

Thanks Claire

 

[ClaireS]

You're very welcome - happy to help.

 

[FormR]

Worksheet Formulas Cell Formula G2 =IF(<font color="Blue">ISNUMBER(E2),"Completed",IF(ISNUMBER(D2),IF(D2<today(),"past due",if(AND(D2>=TODAY(),D2<=TODAY()+10),"Nearly Due","In Progress"</today(),"past>)),"-")) <tbody> </tbody>

<tbody>
</tbody>

Hi, FWIW - we don't need that AND(D2>=TODAY()...) test in the 4th nested IF(), we already know it must be otherwise it wouldn't have got past the 3rd nested IF()

=IF(ISNUMBER(E10),"Completed",IF(ISNUMBER(D10),IF(D10 < TODAY(),"Past Due",IF(D10<=TODAY()+10,"Nearly Due","In Progress")),"-"))

<today(),"past due",if(d8<="TODAY()+10,"Nearly" due","in="" progress")),"-"))[="" code]<="" html=""></today(),"past>

 

[ClaireS]

Good call! I put it in because I was editing from the formula in #13 which used different logic. Not necessary in mine.

Also, weird how the quote changed the formula, adding a spurious "D2" before my "AND". Something to watch out for.