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:

[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

 

[Paul1895]

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.

 

[Paul1895]

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

Lets try that again.... IF(E10<TODAY(), "Overdue", "In Progress")

 

[Paul1895]

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.

 

[FormR]

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

 

[Paul1895]

Ahh, that makes sense.

Thanks Works great!

Appreciate the assistance.

 

[Paul1895]

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

 

[DSCfromCFA]

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

 

[Paul1895]

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

<tbody>
[TD="colspan: 6"]*Dates based on today being 27/5/2019
[/TD]

</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

 

[ClaireS]

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.