+ Reply to Thread
Results 1 to 6 of 6

Formula to figure out the percent up to date based on a date vs. today's date

  1. #1
    Registered User
    Join Date
    11-24-2015
    Location
    Peoria, IL
    MS-Off Ver
    2013
    Posts
    2

    Formula to figure out the percent up to date based on a date vs. today's date

    I have attached a part of the excel sheet to help in understanding the problem.
    I need a formula/formulas to help figure out the percentage on time based on the current date. I have a current Gate L-3 and the next Gate Date. I need the formula to look at the current gate and see if a date is filled into all the boxes before that gate. Example, if the current gate is 3, then there needs to be a date filled into Gate L, 1, & 2. If it is up to date on that information, then it is 100%. If a date is missing, example current gate is 3 but there are only dates filled into Gate L & 1, then the formula needs to figure out the percentage that is up to date. So three gates need to be filled in for 100%, but only 2 of the 3 are filled in so that percentage would be 66.7%. Then I need to take on the percentages from each row and have a total project percentage.
    I hope the excel sheet attached helps understand the issue. And questions please let em know. This problem is a bit advanced for me to fully figure out.Test.xlsx
    Last edited by rwelker; 12-01-2015 at 02:07 PM.

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Formula to figure out the percent up to date based on a date vs. today's date

    Sounds like, in L2 copied down

    =IF($E2="L", 1,COUNT($G2:INDEX($G2:$J2,$E2))/$E2)
    format cell as Percentage
    Does that work for you?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Formula to figure out the percent up to date based on a date vs. today's date

    Maybe in L2:

    =IFERROR(COUNTA(G2:J2)/E2,"")

    Drag down

    In L10:

    =COUNTA(G2:I7)/SUM(E2:E7)
    Quang PT

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Formula to figure out the percent up to date based on a date vs. today's date

    another variant...

    =IF(AND(E2="L",COUNT(G2:J2)=0),100,100*COUNT(G2:J2)/E2)
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  5. #5
    Registered User
    Join Date
    11-24-2015
    Location
    Peoria, IL
    MS-Off Ver
    2013
    Posts
    2

    Re: Formula to figure out the percent up to date based on a date vs. today's date

    I thought this was solved, but appears like it is not as I look deeper. The formulas work, as long as the dates are not past. Since none of the formulas look at the Next Gate Date, it is giving false percentages. For example, row five shows that gate 3 should be completed based on the date being past. The formulas show that it is 100%, but in reality it is only 75% complete. Does anyone have any suggestions on how to create a formula that looks at that date and sees if it is past the current date?

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,004

    Re: Formula to figure out the percent up to date based on a date vs. today's date

    Try ..

    =IFERROR(IF(F2<TODAY(),COUNTA(G2:J2)/E2,COUNTA(G2:J2)/(COUNTA(G2:J2)+1)),"")

+ 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] Formula to compare date (including month and year) from a listed date to today's date
    By mhewitson15 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-11-2014, 05:31 PM
  2. Replies: 1
    Last Post: 09-30-2012, 03:01 PM
  3. Conditional Formating of cells based on today's date, and another date
    By dviolante10 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-24-2012, 02:32 PM
  4. Need Macro Date Help not based on Today's Date
    By Sally_Shell in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-19-2011, 08:57 PM
  5. Need Macro Date Help not based on Today's Date
    By Sally_Shell in forum Excel General
    Replies: 1
    Last Post: 05-18-2011, 08:22 PM
  6. Display a Value based on Today's Date in Date Range
    By barella in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-12-2010, 06:25 AM
  7. MAX figure within a date range as a function of today()'s date
    By irvine79 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-06-2006, 12:45 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