+ Reply to Thread
Results 1 to 5 of 5

Return value based on two project dates: Completed, Ongoing, Upcoming

  1. #1
    Registered User
    Join Date
    04-26-2021
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    2

    Smile Return value based on two project dates: Completed, Ongoing, Upcoming

    Hello,

    I was hoping to find a formula that would help me get a an update on the status of a project based on two dates.

    Very simply put, I wish there was a formula out there that would look at both dates and return a value based on today's date:
    • Completed
    • Ongoing
    • Upcoming

    I have the start date in B and end date in C and would like the coveted formula in A.

    I attached a screenshot to (hopefully) make it clearer. I've tested out IF and AND formulas but have hit a wall.

    Any help would be much appreciated - even if it's to tell me I'm chasing something that doesn't exist

    Thanks in advance!
    Attached Images Attached Images
    Last edited by nicoke; 04-26-2021 at 06:48 PM. Reason: posted screenshot twice by error

  2. #2
    Registered User
    Join Date
    01-11-2021
    Location
    CANADA
    MS-Off Ver
    Excel 2007, Office 2019
    Posts
    31

    Re: Return value based on two project dates: Completed, Ongoing, Upcoming

    A2: =LOOKUP(MEDIAN(B2,C2,TODAY())-TODAY(),{-1E+99,0,1},{"Completed","Ongoing","Upcoming"})

  3. #3
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,803

    Re: Return value based on two project dates: Completed, Ongoing, Upcoming

    Another option:
    A2 and copy down:

    =IF(TODAY()>C2,"Completed",IF(TODAY()<B2,"Upcoming","Ongoing"))

  4. #4
    Registered User
    Join Date
    04-26-2021
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    2

    Re: Return value based on two project dates: Completed, Ongoing, Upcoming

    Thank you both! Really appreciate the help.

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Return value based on two project dates: Completed, Ongoing, Upcoming

    Another way.

    Formula: copy to clipboard
    Please Login or Register  to view this content.



    A
    B
    C
    1
    4/27/2021
    Start
    End
    2
    ongoing
    3/21/2021
    6/30/2021
    3
    completed
    12/9/2020
    4/5/2021
    4
    upcoming
    6/1/2021
    7/1/2021
    Dave

+ 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. [SOLVED] Sum number of project completed in year, ignoring multiple entries of same project number
    By matrixpom in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-11-2021, 12:28 AM
  2. [SOLVED] Return upcoming/past date based on date
    By Klopsik in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-08-2018, 05:15 AM
  3. Help with Traffic Light System based on upcoming Training Expiry dates
    By blobra21 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-19-2018, 03:25 AM
  4. Replies: 6
    Last Post: 06-06-2014, 07:40 AM
  5. Replies: 11
    Last Post: 05-04-2014, 08:28 PM
  6. [SOLVED] Template for tracking project completion dates and simple metrics? % Completed on time
    By Shrad013 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-19-2013, 10:28 AM
  7. Completed and remaining days count based on the dates range
    By krjoshi in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-21-2011, 01:59 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