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

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:

START
(D9)
DUE
(E9)
COMPLETED
(F9)
STATUS
(G9)
1-1-20193-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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
re: IF due date cell is < today = "" unless completed cell contains a date

Sorry, i must have made an error because the above post did not contain the full formula i had entered into G10. It is =IF(E10<TODAY(), "Overdue", "In Progress")

Thanks.
 
Upvote 0
re: IF due date cell is < today = "" unless completed cell contains a date

Lets try that again.... IF(E10<TODAY(), "Overdue", "In Progress")
 
Upvote 0
Sorry everyone, my lack of experience with these forums is clearly obvious by my last two posts. :(

The formula i currently have in G10 made it so that it would show as "In Progress" if the date in E10 was after todays date and "Overdue" if it was before todays date.

I would like to add to that formula so that it only came into play if there is no date in F10. If there is a date in F10, then it would show as "Completed".

Thanks.
 
Upvote 0
Hi, welcome to the forum!

Your formula isn't displaying because the forum software is misinterpreting the less than symbol as HTML and it's trying to parse it. A work around is to put spaces before and after those symbols when posting the formula.

Here is an option you can try though.

=IF(ISNUMBER(F10),"Completed",IF(E10 < TODAY(), "Overdue", "In Progress"))
 
Upvote 0
Hi again.

Firstly, thanks again for your assistance with my first question.

After the first trial of my project management spreadsheet a few issues popped up almost instantly. I was able to fix most of them except for the following:

Issue: It has been pointed out to me that a due date will not always be known when a task is first added meaning that the DUE column will at times contain cells with no dates in them. The problem with this is without a date in the DUE column the STATUS column will always read as "Overdue".

Q: Is there a way to modify the formula for the cells in the STATUS column, which is currently =IF(ISNUMBER(F10),"Completed",IF(E10 < TODAY(), "Overdue", "In Progress")) , so that it reads as "In Progress" if the cells in the DUE column are left blank?

Note: As i wasn't sure if i should create a new thread for this question i am not able to insert a table in this post to give some context. Please scroll up to my original post in this thread for the table.

Any assistance is greatly appreciated.

Thanks in advance.

Paul
 
Upvote 0
G'day Paul,

A quick fix (which admittedly may lead to some confusion) is to put a due date of =Today()+1 in the cells with no end date - this will keep the due date always ahead of the actual date.

The potential confusion may arise if you don't go in and add a fixed due date when it becomes known - however a bit of conditional formatting may highlight such exceptions.

Cheers

shane
 
Upvote 0
Thanks for the quick response and suggested fix Shane.

As i wont be on hand to assist the eventual users of this spreadsheet with any issues/questions they may have, my aim is to get it to a stage where it looks like a blank template with only the STATUS column pre-populated with the default "In Progress" when nothing has been entered in the cells yet.

Ideally, the other three columns (START, DUE & END) will all have "-" in the cells below it and the STATUS will have "In Progress" until one of the following occurs:


START
DUE
END

STATUS
1.
-
-
-
=
IN PROGRESS
2.
1/1/2019
-
-
=
IN PROGRESS

1/1/2019
28/5/2019
-
=
IN PROGRESS

-
28/5/2019
-
=
IN PROGRESS
3.
-
-
15/2/2019
=
COMPLETED
4.
1/1/2019
7/2/2019
15/2/2019
=
COMPLETED
5.
1/1/2019
-
15/2/2019
=
COMPLETED
6.
-
7/2/2019
15/2/2019
=
COMPLETED
7.
1/1/2019
7/2/2019
-
=
OVERDUE
8.
-
7/2/2019
-
=
OVERDUE
*Dates based on today being 27/5/2019

<tbody>
</tbody>

I may be asking for something that isnt possible but i thought i would put it out there and see if there is a formula for the STATUS cells that will give me the above outcomes.

Thanks again :)
 
Upvote 0
How about
=IF(ISNUMBER(F10),"Completed",IF(AND(E10 > 0,E10 < TODAY()), "Overdue", "In Progress"))
This will only show OVERDUE if there is a date in E10.
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

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