Formula for tracking percentage complete

F

fcraven

New Member
Joined
Aug 23, 2008
Messages
3
I have Excel 2007. I track training for my unit. I have a column of 69 dates of which I input. I use a conditional formula in the adjacent column to show green (current), yellow(due within 30 days) and red (overdue). Using the first column or the conditional column, I would like to get a percentage of dates in the column that are current as of today. So everytime I log in I can retrieve a percentage at a quick glance. My first column is O2:O70 or the 69 assigned personnel. Some of the columns may not have a date as new people have taken the class yet. Any help or a start with this would be great.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
something like:
Col F contains dates or blanks
Col G contains either: Current, Due<30, or OverDue

=SUMPRODUCT(--(F2:F15<>""),--(G2:G15="Current"))/COUNTA(F2:F15)
 
Upvote 0
Thanks for the help Jim. Here is a compressed version of what I have. The HR due is the Human Relations Complete Column+365 or C2+365. The color changes from green to yellow if it is within 30 days of going overdue. The red means overdue. I do this using the conditional formatting. I just want to get a percentage of HR is current. According to the example this one is 83.333%. Thanks in advance.


<TABLE style="WIDTH: 304pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=404 border=0><COLGROUP><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3876" width=106><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" width=73><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4315" width=118><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3913" width=107><TBODY><TR style="HEIGHT: 26.25pt" height=35><TD class=xl83 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 80pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 26.25pt; BACKGROUND-COLOR: silver" width=106 height=35>LAST</TD><TD class=xl83 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 55pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver" width=73>FIRST</TD><TD class=xl77 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 89pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: lime" width=118>Human Relations Complete</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 80pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: lime" width=107>HR Due</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl78 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 80pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=106 height=17>Joe 1</TD><TD class=xl79 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: silver; WIDTH: 55pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=73>Bob 1</TD><TD class=xl74 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 89pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=118>2-Jun-08</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: lime" align=right>2-Jun-09</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl80 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: windowtext 1pt solid; WIDTH: 80pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=106 height=17>Joe 2</TD><TD class=xl73 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 55pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=73>Bob 2</TD><TD class=xl75 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: windowtext 1pt solid; WIDTH: 89pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=118>7-Nov-07</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: lime" align=right>6-Nov-08</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl80 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: windowtext 1pt solid; WIDTH: 80pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=106 height=17>Joe 3</TD><TD class=xl73 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 55pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=73>Bob 3</TD><TD class=xl75 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: windowtext 1pt solid; WIDTH: 89pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=118></TD><TD class=xl70 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BACKGROUND: red; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; mso-pattern: black none" align=right>30-Dec-00</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl80 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: windowtext 1pt solid; WIDTH: 80pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=106 height=17>Joe 4</TD><TD class=xl73 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 55pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=73>Bob 4</TD><TD class=xl75 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: windowtext 1pt solid; WIDTH: 89pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=118>1-Dec-07</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: lime" align=right>30-Nov-08</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl80 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: windowtext 1pt solid; WIDTH: 80pt; BORDER-BOTTOM: silver 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=106 height=17>Joe 5</TD><TD class=xl73 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 55pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=73>Bob 5</TD><TD class=xl75 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: windowtext 1pt solid; WIDTH: 89pt; BORDER-BOTTOM: silver 0.5pt solid; BACKGROUND-COLOR: transparent" width=118>25-Aug-07</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BACKGROUND: yellow; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; mso-pattern: black none" align=right>24-Aug-08</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl81 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: windowtext 1pt solid; WIDTH: 80pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=106 height=18>Joe 6</TD><TD class=xl82 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: silver; WIDTH: 55pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=73>Bob 6</TD><TD class=xl76 style="BORDER-RIGHT: silver 0.5pt solid; BORDER-TOP: silver; BORDER-LEFT: windowtext 1pt solid; WIDTH: 89pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=118>2-Jul-08</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: lime" align=right>2-Jul-09</TD></TR></TBODY></TABLE>
 
Upvote 0
Given your example try

=COUNTIF(D2:D7,"<"&TODAY())/COUNT(D2:D7)

although I wouldn't have thought that you would want to count D4 as overdue as it's just based on a blank in C4. Perhaps you should change D2 formula to

=IF(C2="","",C2+365)
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Macro to add sum total to track percentages
Replies
0
Views
102
spenserallen7
S
C
  • Solved
Percent Complete Between Two Dates - When Not Started How To Show Zero Percent
Replies
2
Views
827
Consult123
C
C
  • Locked
  • Question
Percent Complete Versus Start and End Date Formulas For Conditional Formatting
2 3
Replies
23
Views
923
Consult123
C
T
  • Solved
Conditional Format - Column Date Range & Another Variable
Replies
3
Views
66
Joe4
Joe4
D
  • Solved
Syntax error - Excel formulas in VBA code
Replies
5
Views
223
kevin9999
K

Forum statistics

Threads
1,214,659
Messages
6,120,786
Members
448,992
Latest member
prabhuk279

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top