+ Reply to Thread
Results 1 to 19 of 19

Excel 2007 : Conditional Formatting/Summary based on Due/Completion Dates

  1. #1
    Registered User
    Join Date
    03-18-2008
    Posts
    17

    Conditional Formatting/Summary based on Due/Completion Dates

    Spreadsheet attached...

    I'm trying to summarize project status data to calculate:
    A) How many projects are there?
    B) How many are completed?
    C) How many of the projects were completed "on time"?
    D) What projects are coming due, or overdue.

    I started to create a summary, counting projects and "completed" based on whether or not there was a date (numerical value) in the cell...then calculated the percentage based on those two numbers - that was easy.

    Now, I'm having trouble getting the next step - calculating how many of those projects already completed were "late" (completed past the due date), and how many projects are currently overdue.

    After that, I wanted to format the entire rows (shaded Red - overdue, Orange - coming due within 15 days, and yellow - due within 30 days)...

    Any help would be appreciated...thank you.
    Attached Files Attached Files
    Last edited by ProjectMASE; 10-23-2009 at 11:55 AM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Conditional Formatting/Summary based on Due/Completion Dates

    Formula for Late: =SUMPRODUCT(--(E3:E13>D3:D13))

    Formula for Due: =SUMPRODUCT(--(E3:E13=""),--(D3:D13<""),--(D3:D13<TODAY()))

    Conditional formulas applied to A3:E13

    =AND($E3="",$D3<>"",$D3<TODAY()) for Red

    =AND($E3="",$D3>TODAY(),$D3<=TODAY()+15) for Orange

    =AND($E3="",$D3>TODAY(),$D3<=TODAY()+30) for Yellow
    Attached Files Attached Files
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    03-18-2008
    Posts
    17

    Re: Conditional Formatting/Summary based on Due/Completion Dates

    That seems to be what I'm looking for as far as shading the upcoming due dates, thanks!

    However, I can't seem to get the "overdue" formula to work correctly...it's formatting ALL of the dates in column E (Completed) as if they were ALL completed late, and that's not true. What am I missing?

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Conditional Formatting/Summary based on Due/Completion Dates

    Sorry... probably my fault should've been:

    =SUMPRODUCT(--(E3:E13=""),--(D3:D13<>""),--(D3:D13<TODAY()))

    I missed that >

  5. #5
    Registered User
    Join Date
    03-18-2008
    Posts
    17

    Re: Conditional Formatting/Summary based on Due/Completion Dates

    It's still not working quite right...attached what I'm currently working with.

    Also, note how slow the spreadsheet runs with that conditional format in place - remove it, and it's fine...weird.

    Thanks for your help - I feel like we're close.
    Attached Files Attached Files

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Conditional Formatting/Summary based on Due/Completion Dates

    Your spreadsheet is making my XL crash... it's not the conditional formatting unless you have applied it everywhere.

    Try opening my last posted workbook and work with that... then show me the problem using that workbook.

  7. #7
    Registered User
    Join Date
    03-18-2008
    Posts
    17

    Re: Conditional Formatting/Summary based on Due/Completion Dates

    ha ha - sorry...what I did was applied the corrected formula as a 4th rule in conditional formatting, with the intent of trying to get it to BOLD and RED any completed dates entered beyond the corresponding due date for the same line.

    I double-checked how I was applying it, and I guess I WAS applying it to the entire sheet...whoops! Fixed now, so I'm only applying it to the first 4 projects (G1a-G1d)
    Attached Files Attached Files

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Conditional Formatting/Summary based on Due/Completion Dates

    I am not sure anymore what you want...

    You still have the Sumproduct formula applied as a conditional format.. and it wasn't intended for that...

    Also, you have merged cells for Project G.1b and conditional formatting doesn't work on those... so that is why only part of the row is coloured....

  9. #9
    Registered User
    Join Date
    03-18-2008
    Posts
    17

    Re: Conditional Formatting/Summary based on Due/Completion Dates

    I'm sorry, you're right...I guess I wasn't clear in my initial post.

    Aside from calculating/displaying the number of projects there were completed "late", I am also looking to make those particular projects standout on the sheet via conditionally formatting the completed date. (as of last night, my boss would ALSO like to see the ones that were completed on-time in bold/green - so the report isn't so negatively toned...LoL)

    From what you're saying, I can't use the same formula that is used to calculate the total number of late projects to conditionally format the specific results, as well...which, as I typed that, actually makes sense. However, what in the formula would need to change so that it WILL format the results correctly?

    Thanks again for all your help.

  10. #10
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Conditional Formatting/Summary based on Due/Completion Dates

    I don't have access to XL2007 today.. but I suggest you clear all conditional formatting and then restart by entering these again:... In my test, they worked at identifying orders that overdue, ones that were within 15 days and ones that were within 30 days of coming due...

    =AND($E3="",$D3<>"",$D3<TODAY()) for Red

    =AND($E3="",$D3>TODAY(),$D3<=TODAY()+15) for Orange

    =AND($E3="",$D3>TODAY(),$D3<=TODAY()+30) for Yellow

    (note, it was assumed that if there is a date in the Completed column, then you wouldn't want that row highlighted)

    For Completed, simply add condition:

    =$E$3<>"" and format as desired.

  11. #11
    Registered User
    Join Date
    03-18-2008
    Posts
    17

    Re: Conditional Formatting/Summary based on Due/Completion Dates

    The shading of the rows works perfectly...I've got it all plugged into the real spreadsheet and it's working like a charm.


    Am I adding that last part of the function to

    =SUMPRODUCT(--(E3:E13>D3:D13))=$E$3<>""

    since I want to bold/red the project completion dates that were completed late?

  12. #12
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Conditional Formatting/Summary based on Due/Completion Dates

    I think assuming there is a date in Column D when there is a date in column E, then an additional Condition of:

    =$E3>$D3 with formats applied

    will work

  13. #13
    Registered User
    Join Date
    03-18-2008
    Posts
    17

    Re: Conditional Formatting/Summary based on Due/Completion Dates

    correct, Column D is the "due date" while Column E is the completed date - there will ALWAYS be a date in Column D, and Column E will only ever get a date once the project is completed.

    So, I've input the formula: =SUMPRODUCT(--(E3:E13>D3:D13))=$E$3>$D$3 with formatting as I'd like, and it just formats EVERY date in Column E, despite their being only 2 qualifying "late" projects.

    I also tried just =$E$3>$D$3, thinking I was over-engineering things, and all it did was remove all of the formatting that the previous formula had applied - as if NONE of the projects qualified now...need the happy medium...lol

  14. #14
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Conditional Formatting/Summary based on Due/Completion Dates

    Try making the =$E$3>$D$3 condition first in the list of conditional formats and make sure to tick the Stop if true checkboxes.

    Forget the Sumproduct() formula in the conditional formatting.

    Like I said I can't test in 2007, but I tested in 2003 with only a couple of conditions and it worked...
    Last edited by NBVC; 10-23-2009 at 11:16 AM.

  15. #15
    Registered User
    Join Date
    03-18-2008
    Posts
    17

    Re: Conditional Formatting/Summary based on Due/Completion Dates

    that doesn't seem to have an effect...I've got to be missing something small here, it seems like it should work, as-is.

  16. #16
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Conditional Formatting/Summary based on Due/Completion Dates

    Excel2003 only allows 3 conditions.. so I tested your last posted sheet above and just removed one condition... and it worked....

    I even tried it again replacing 2nd condition and it worked...

    Try posting your current workbook, and I can see if XL2003 shows me at least where there is a problem....
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    03-18-2008
    Posts
    17

    Re: Conditional Formatting/Summary based on Due/Completion Dates

    interesting...
    Attached Files Attached Files

  18. #18
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Conditional Formatting/Summary based on Due/Completion Dates

    I noticed in Post 13 above, you mentioned you tried:

    =$E$3>$D$3

    should that be?

    =$E3>$D3

  19. #19
    Registered User
    Join Date
    03-18-2008
    Posts
    17

    Re: Conditional Formatting/Summary based on Due/Completion Dates

    Quote Originally Posted by NBVC View Post
    I noticed in Post 13 above, you mentioned you tried:

    =$E$3>$D$3

    should that be?

    =$E3>$D3
    That's it, you're a genius! I knew it was something simple...too funny - LoL.

    Thank you for ALL of your help and patience on this one!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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