# How to find the Percent of a current training dates?

1. ## How to find the Percent of a current training dates?

Hello,

This is my first time posting so I hope I am doing it correctly. I am having a rough time trying to figure out a formula. I have a spreadsheet that I track the start date of training for 20 co-workers. The spreadsheet then generates due dates. Since there are over 100 training items on my list I use Condition Formatting to highlight the cells that are within 30 days of becoming delinquent. The goal is I would like to apply a formula to calculate the percentage of training that is current for an individual. The setup I have so far. Sheet1 has names across Row1, and the training going down Column A. Then start dates and due dates in all the appropriate spots. Sheet2 is set up similar but with the formula =IF(ISNUMBER(Sheet1!B3<TODAY()-730)=TRUE,1) I know this formula isn't quiet right, but I'm trying to get the cell to input a 1 if complete and current within two years. Once the formula is correct, I would apply a formula to add the SUM of B3:B100 then divide by total number of training items to get the percentage. I just don't know how to apply the first half the formula! HELP PLEASE! It will be appreciated greatly if you can help me out!

extra info: The training expires every two years and needs to be re-accomplished.
I have attached a draft of my spreadsheet.

Thank you
-JMA  Register To Reply

2. ## Re: How to find the Percent of a current training dates?

Try D4 down:
=IF(AND(Sheet1!C4>=TODAY()-730,Sheet1!D4>=TODAY()),1,0).
Do you need the extra sheet(Formula?) as this can be done without it.  Register To Reply

3. ## Re: How to find the Percent of a current training dates?

Your formula worked perfectly! Thank you!   Register To Reply

4. ## Re: How to find the Percent of a current training dates? Originally Posted by JAllred555 Your formula worked perfectly! Thank you! You are welcome.  Register To Reply