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

P

Paul1895

New Member
Joined
May 23, 2019
Messages
14
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:

[TABLE="class: grid, width: 700, align: center"]
<tbody>[TR]
[TD="align: center"]START
(D9)[/TD]
[TD="align: center"]DUE
(E9)[/TD]
[TD="align: center"]COMPLETED
(F9)[/TD]
[TD="align: center"]STATUS
(G9)[/TD]
[/TR]
[TR]
[TD="align: center"]1-1-2019[/TD]
[TD="align: center"]3-1-2019[/TD]
[TD="align: center"][/TD]
[TD="align: center"]=IF(E10<TODAY(), "Overdue", "In Progress")[/TD]
[/TR]
</tbody>[/TABLE]

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
 
Sort by date Sort by votes
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:

[TABLE="width: 883"]
<tbody>[TR]
[TD][/TD]
[TD](A)[/TD]
[TD](B)[/TD]
[TD](C)[/TD]
[TD](D)[/TD]
[TD](E)[/TD]
[TD](F)[/TD]
[TD](G)[/TD]
[/TR]
[TR]
[TD](1)[/TD]
[TD]ACTION[/TD]
[TD]LEAD[/TD]
[TD]START[/TD]
[TD]DUE[/TD]
[TD]END[/TD]
[TD]%[/TD]
[TD]STATUS[/TD]
[/TR]
[TR]
[TD](2)[/TD]
[TD]Write up project plan.[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]0%[/TD]
[TD]-[/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="width: 883"]
<tbody>[TR]
[TD][/TD]
[TD](A)[/TD]
[TD](B)[/TD]
[TD](C)[/TD]
[TD](D)[/TD]
[TD](E)[/TD]
[TD](F)[/TD]
[TD](G)[/TD]
[/TR]
[TR]
[TD](1)[/TD]
[TD]ACTION[/TD]
[TD]LEAD[/TD]
[TD]START[/TD]
[TD]DUE[/TD]
[TD]END[/TD]
[TD]%[/TD]
[TD]STATUS[/TD]
[/TR]
[TR]
[TD](2)[/TD]
[TD]Write up project plan.[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]23-5-19[/TD]
[TD]-[/TD]
[TD]0%[/TD]
[TD]Almost Due
[/TD]
[/TR]
</tbody>[/TABLE]


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

Hope this makes sense.

Thanks in advance for any assistance people can provide :)

Paul
 
Last edited:
Upvote 0

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
ERROR MADE IN PREVIOUS POST - PLEASE REFER TO THIS ONE INSTEAD


Here is what i have now:

[TABLE="class: cms_table, width: 883"]
<tbody>[TR]
[TD][/TD]
[TD](A)[/TD]
[TD](B)[/TD]
[TD](C)[/TD]
[TD](D)[/TD]
[TD](E)[/TD]
[TD](F)[/TD]
[TD](G)[/TD]
[/TR]
[TR]
[TD](1)[/TD]
[TD]ACTION[/TD]
[TD]LEAD[/TD]
[TD]START[/TD]
[TD]DUE[/TD]
[TD]END[/TD]
[TD]%[/TD]
[TD]STATUS[/TD]
[/TR]
[TR]
[TD](2)[/TD]
[TD]Write up project plan.[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]0%[/TD]
[TD]-[/TD]
[/TR]
</tbody>[/TABLE]


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?

[TABLE="class: cms_table, width: 883"]
<tbody>[TR]
[TD][/TD]
[TD](A)[/TD]
[TD](B)[/TD]
[TD](C)[/TD]
[TD](D)[/TD]
[TD](E)[/TD]
[TD](F)[/TD]
[TD](G)[/TD]
[/TR]
[TR]
[TD](1)[/TD]
[TD]ACTION[/TD]
[TD]LEAD[/TD]
[TD]START[/TD]
[TD]DUE[/TD]
[TD]END[/TD]
[TD]%[/TD]
[TD]STATUS[/TD]
[/TR]
[TR]
[TD](2)[/TD]
[TD]Write up project plan.[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]5-6-19[/TD]
[TD]-[/TD]
[TD]0%[/TD]
[TD]Almost Due[/TD]
[/TR]
</tbody>[/TABLE]



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

Hope this makes sense.

Thanks in advance for any assistance people can provide :smile:

Paul
 
Upvote 0
Hi, does this cover all the scenarios?


Excel 2013/2016
ABCDEFG
1ACTIONLEADSTARTDUEEND%STATUS
2Write up project plan.--05/06/2019-Nearly Due
328/05/2019Past Due
404/05/2018Completed
5-
Sheet1
Cell Formulas
RangeFormula
G2=IF(ISNUMBER(E2),"Completed",IF(ISNUMBER(D2),IF(D2),"Past Due",IF(D2>TODAY()-10,"Nearly Due","In Progress")),"-"))
 
Upvote 0
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
ACTIONLEADSTARTDUEEND%STATUS
Write up project plan.---
Past Due
Completed
-

<tbody>
[TH="align: center"][/TH]
[TH="align: center"]A[/TH]
[TH="align: center"]B[/TH]
[TH="align: center"]C[/TH]
[TH="align: center"]D[/TH]
[TH="align: center"]E[/TH]
[TH="align: center"]F[/TH]
[TH="align: center"]G[/TH]

[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]05/06/2019[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00"]Nearly Due[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]28/05/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]04/05/2018[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
In Progress

<tbody>
[TD="align: center"]6[/TD]
[TD="align: right"]-[/TD]
[TD="align: right"]-[/TD]
[TD="align: right"]-[/TD]
[TD="align: right"]10/07/2019[/TD]
[TD="align: right"]-[/TD]
[TD="align: right"]-[/TD]

</tbody>
In Progress

<tbody>
[TD="align: center"]7[/TD]
[TD="align: right"]-[/TD]
[TD="align: right"]-[/TD]
[TD="align: right"]-[/TD]
[TD="align: right"]12/06/2019[/TD]
[TD="align: right"]-[/TD]
[TD="align: right"]-[/TD]

</tbody>
Nearly Due

<tbody>
[TD="align: center"]8[/TD]
[TD="align: right"]-[/TD]
[TD="align: right"]-[/TD]
[TD="align: right"]-[/TD]
[TD="align: right"]08/06/2019[/TD]
[TD="align: right"]-[/TD]
[TD="align: right"]-[/TD]

</tbody>
Nearly Due

<tbody>
[TD="align: center"]9[/TD]
[TD="align: right"]-[/TD]
[TD="align: right"]-[/TD]
[TD="align: right"]-[/TD]
[TD="align: right"]31/05/2019[/TD]
[TD="align: right"]-[/TD]
[TD="align: right"]-[/TD]

</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>)),"-"))
 
Last edited:
Upvote 0
Here you go :)

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
Book1
ABCDEFG
1ACTIONLEADSTARTDUEEND%STATUS
2write pp--05/06/2019-Nearly Due
328/06/2019In Progress
404/05/2018Completed
510/07/2019In Progress
612/05/2019Past Due
708/06/2019Nearly Due
831/05/2019Nearly Due
Sheet1
Cell Formulas
RangeFormula
G2=IF(ISNUMBER(E2),"Completed",IF(ISNUMBER(D2),IF(D2),"Past Due",IF(AND(D2>=TODAY(),D2<=TODAY()+10),"Nearly Due","In Progress")),"-"))
[/FONT]
 
Upvote 0
ClaireS said:
[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]G2[/TH]
[TD="align: left"]=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>)),"-"))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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()

Code: 
=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>
 
Last edited:
Upvote 0
Good call! I put it in because I was editing from the formula in #13 which used different logic. Not necessary in mine. :rolleyes:

Also, weird how the quote changed the formula, adding a spurious "D2" before my "AND". Something to watch out for.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
If a cell contains the word "Completed", then return the date/time when it changed.
2
Replies
14
Views
768
dreid1011
dreid1011
R
  • Question Question
Automatic Status update depending on data entered
Replies
5
Views
696
dreid1011
dreid1011
E
  • Question Question
Stop counting cells when status is complete
Replies
4
Views
407
estanden
E
M
  • Question Question
Highlight row based on first date found within cell
Replies
6
Views
798
Asbestos_Jen
Asbestos_Jen
Bonnie Janes
  • Question Question
Microsoft 365 Excel IF/Due/Overdue Formula Problem
2
Replies
11
Views
1K
Void_
V

Forum statistics

Threads
1,223,898
Messages
6,175,272
Members
452,628
Latest member
dd2

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top