+ Reply to Thread
Results 1 to 11 of 11

Progress bar based on dates

  1. #1
    Forum Contributor
    Join Date
    09-19-2018
    Location
    England
    MS-Off Ver
    Professional Plus 2013
    Posts
    256

    Progress bar based on dates

    Hi all

    I have a manually input progress bar in column E via conditional format, what I am trying to do is have this progress bar linked to a date so the closer to the date the more percentage of the bar is completed.

    The date for this I would like to link to is in column R which is the estimated end date.

    Is this possible?

    Many thanks
    Attached Files Attached Files
    Last edited by MattExcelLearner; 05-26-2020 at 08:33 AM. Reason: Solved

  2. #2
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    845

    Re: Progress bar based on dates

    Hi Matt,
    We have the Project end date in column R (ProjectPlan sheet), which I would assume would be 100% once reached.

    What is missing is the starting point, 0%. I have assumed 'Initiate Baseline Start Date' for testing the formula.
    This would mean we have a start date of 15/05/2015 and an End date of 5/04/2022. This would give us a 72.98% completed.
    This figure then can be indexed back to your ProjectPlan sheet and the conditional formatting should take care of the rest.
    Refer to the owssvr sheet, column R for the formula/calculation. If a different start date is to be used, adjust the formula accordingly.
    If a project has reached/passed the completion date, the progress will be 100%.

    I trust this gives you some ideas on the progress bar issue.
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    09-19-2018
    Location
    England
    MS-Off Ver
    Professional Plus 2013
    Posts
    256

    Re: Progress bar based on dates

    Hi ORoos

    That seems to work which is cool (HUGE THANK YOU) but was wondering where estimated end date in column Q is blank would the formula be able to be expanded to then look at column N the closure baseline end date?

    This will then mean all percentages are correct and wont show 0% or minus percentages.


    That would be really cool

  4. #4
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    845

    Re: Progress bar based on dates

    Hi Matt,

    I have added a helper-column in the table (red font): if there is an 'Estimated End Date', take that, if not available, take the 'Closure Baseline End Date'. If neither of the two dates are entered = blank.

    Much easier to use a helper-column than creating a monster of a formula in one cell. The helper-column can be hidden if you don't want it on clutter up your sheet, but I normally leave it for transparency.

    One more variable: the start date in column 'G'. I assume there will always be a date. If not, the percentage will be incorrect.

    Trust this helps.
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    09-19-2018
    Location
    England
    MS-Off Ver
    Professional Plus 2013
    Posts
    256

    Re: Progress bar based on dates

    Cool that worked like a charm, worked through what you did to my live one and works - sorry for late reply had to work it all out and understand

    G will always have a date from database so that's good.

    This really does help.

    I just have to work out now how to hide the date 00/01/1900 in column R on first tab. (Estimated end) when there is no date pulling through on the index match:

    =INDEX(owssvr!$Q:$Q,MATCH(B10,owssvr!$A:$A,FALSE))

    It comes through 00/01/1900

    I have tried conditional format to say if specific text = 00/01/1900 font go white but didn't work and now stumped

  6. #6
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    845

    Re: Progress bar based on dates

    Given that we replace the 'Estimate End Date' (column Q) with the 'Closure Baseline End Date' (column N), would it make sense to lookup the new helper column R which will have a date? This date then would be consistent with the progress bar shown.

    Alternatively, try the formula below in in your first sheet.
    =IF(INDEX(owssvr!$Q:$Q,MATCH(B10,owssvr!$A:$A,FALSE))="","",INDEX(owssvr!$Q:$Q,MATCH(B10,owssvr!$A:$A,FALSE)))

  7. #7
    Forum Contributor
    Join Date
    09-19-2018
    Location
    England
    MS-Off Ver
    Professional Plus 2013
    Posts
    256

    Re: Progress bar based on dates

    Yeah I would of taken from R but need to keep separate so people know the difference between the actual date which has been signed off and the estimated which is the date working to but not approved. Hence the grey bar showing this in timeline.

    The formula below works but unfortunately showing no date makes my estimated grey bar go on for eternity which isn't good. Kind of just need to hide the text so its invisible haha

    the conditional formula for the grey bar is: =AND($R8>=S$5,$G8<T$5)

  8. #8
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    845

    Re: Progress bar based on dates

    If you just want to 'hide' the "00/01/1900", try adding conditional formatting onto column R.
    Format only cells that contain > Format only cells with > Cell Value equal to 0 (zero)
    then under format choose Font colour = White

  9. #9
    Forum Contributor
    Join Date
    09-19-2018
    Location
    England
    MS-Off Ver
    Professional Plus 2013
    Posts
    256

    Re: Progress bar based on dates

    Awesome that is what I did wrong! - I did "if specific text = 00/01/1900 font go white" hahaha

    I feel like an idiot! - I should of known that as its an index match to pull the information into a cell.

    ORoos thanks for your help today m8 much appreciated woooooooo all works

  10. #10
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    845

    Re: Progress bar based on dates

    You are very welcome. Glad to hear all is working.

  11. #11
    Forum Contributor
    Join Date
    09-19-2018
    Location
    England
    MS-Off Ver
    Professional Plus 2013
    Posts
    256

    Re: Progress bar based on dates

    Thanks again dude

+ 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] Calculate progress between two dates
    By rjyusmc2005 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-09-2020, 10:50 AM
  2. Calculate Percentage progress between two dates
    By SuperFlrares in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 10-30-2018, 11:16 AM
  3. Progress Tracker based on dates
    By pauldaddyadams in forum Excel General
    Replies: 6
    Last Post: 03-02-2017, 01:24 PM
  4. [SOLVED] Current progress calculation based on dates
    By kriminaal in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-29-2016, 04:13 PM
  5. Progress-Chart with different start/end Dates
    By tingle in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-05-2016, 07:47 AM
  6. Need formula: progress (% completion) between two dates
    By bsafra1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-17-2014, 09:53 AM
  7. [SOLVED] Conditional Formatting - Progress Bar based on dates in row
    By RichTea88 in forum Excel General
    Replies: 3
    Last Post: 01-24-2013, 08:50 AM

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