+ Reply to Thread
Results 1 to 9 of 9

How to show days overdue only when task hasnt been completed

  1. #1
    Registered User
    Join Date
    07-11-2016
    Location
    manchester, england
    MS-Off Ver
    365 proplus
    Posts
    3

    How to show days overdue only when task hasnt been completed

    So let me describe my sheet:

    Column G = Deadline
    Column H = Days Left to Complete
    Column I = Days Overdue
    Column J = Date Completed

    I want to show the number of days overdue BUT only when the task is actually overdue (i.e i dont want it saying -2) AND i dont want it to show anything if the column G is blank.

    So far i have managed to produce this formula which works for Days left to complete: =IF(AND(G8>TODAY(), J8=""),G8-TODAY(), "")

    I have tried using this formula for days overdue but it just doesnt seem to be working how i want it too: =IF(OR(G8>TODAY(), AND(G8="", J8="")), "", G8-TODAY())

    Any help would be greatly appreciated.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,819

    Re: How to show days overdue only when task hasnt been completed

    This ..

    for Overdue

    =IF(OR(G8>TODAY(), AND(G8="", J8="")), "", TODAY()-G8)

  3. #3
    Registered User
    Join Date
    07-11-2016
    Location
    manchester, england
    MS-Off Ver
    365 proplus
    Posts
    3

    Re: How to show days overdue only when task hasnt been completed

    hmmm that still didnt seem to solve it. I feel I need another OR clause in there. I know this formula doesnt make sense excel wise but do you catch my drift??

    =IF(OR(G8>TODAY(), AND(G8="", J8="")), "", TODAY()-G8) OR J8<G8, "", TODAY()-G8)

    I just dont know how to integrate the OR clause thats in bold into it.

  4. #4
    Registered User
    Join Date
    11-24-2017
    Location
    Hyderabad
    MS-Off Ver
    2013
    Posts
    5

    Re: How to show days overdue only when task hasnt been completed

    well, i think that you could simple formula like to calculate the days left to complete using =G9-TODAY() however, your ask is hide the cell value if it negative.

    in that case, we can use the conditional formatting to hide the cells with -ve values
    https://www.extendoffice.com/documen...e-numbers.html

    Hope this helps you out

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,059

    Re: How to show days overdue only when task hasnt been completed

    It seems to me that the formula could be written like this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  6. #6
    Registered User
    Join Date
    07-11-2016
    Location
    manchester, england
    MS-Off Ver
    365 proplus
    Posts
    3

    Re: How to show days overdue only when task hasnt been completed

    I think you cracked it! cheers pal!!!

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,059

    Re: How to show days overdue only when task hasnt been completed

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

  8. #8
    Registered User
    Join Date
    12-16-2018
    Location
    Condell Park
    MS-Off Ver
    365 office
    Posts
    2

    Re: How to show days overdue only when task hasnt been completed

    hI JeteMc,

    i found the formula doesnt remove value if it is past the target date. it only removes values if the completed date is before the due date.
    can you please help to remove the value when the job is completed. Thanks

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,497

    Re: How to show days overdue only when task hasnt been completed

    Jusjos

    Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Transfer completed task to another sheet
    By drnote5238 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-22-2017, 09:27 AM
  2. transfer completed task to a different worksheet
    By drnote5238 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-12-2017, 06:31 AM
  3. Calculating TAT Received and Completed task
    By hecgroups in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-16-2015, 01:06 PM
  4. Conditional format - red if overdue, no color if completed
    By FlowerJones in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-16-2014, 01:42 PM
  5. [SOLVED] Formula for Days Overdue if not Completed
    By excelnewb02 in forum Excel General
    Replies: 5
    Last Post: 09-11-2012, 04:51 AM
  6. Completed task indicator in Gantt
    By excelhelp18 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-13-2009, 06:52 AM
  7. Overdue/late completion/completed tasks
    By BRISBANEBOB in forum Excel General
    Replies: 6
    Last Post: 01-14-2009, 04:04 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1