Page 1 of 2 12 LastLast
Results 1 to 10 of 19

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

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

    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

  2. #2
    New Member
    Join Date
    May 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    Thanks.

  3. #3
    New Member
    Join Date
    May 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Lets try that again.... IF(E10

  4. #4
    New Member
    Join Date
    May 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  5. #5
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    6,092
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    2 Thread(s)

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

    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"))
    [code]your code[/code]

  6. #6
    New Member
    Join Date
    May 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Ahh, that makes sense.

    Thanks Works great!

    Appreciate the assistance.

  7. #7
    New Member
    Join Date
    May 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  8. #8
    Board Regular
    Join Date
    Feb 2018
    Location
    Oz
    Posts
    119
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

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

    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

  9. #9
    New Member
    Join Date
    May 2019
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

    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

  10. #10
    Board Regular
    Join Date
    Jul 2013
    Location
    Essex, UK
    Posts
    128
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

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

    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.

    Helping you to Excel

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •