+ Reply to Thread
Results 1 to 4 of 4

How to find the Percent of a current training dates?

  1. #1
    Registered User
    Join Date
    08-03-2013
    Location
    Spokane, Washington
    MS-Off Ver
    Excel 2010 and 2011
    Posts
    4

    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
    Attached Files Attached Files

  2. #2
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    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.
    If you are http://www.excelforum.com/image.php?type=sigpic&userid=125481&dateline=1392355029happy with the results, please add to the contributor's
    reputation by clicking the reputation icon (star icon).




    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.
    To undo, select Thread Tools-> Mark thread as Unsolved.
    http://www.excelaris.co.uk

  3. #3
    Registered User
    Join Date
    08-03-2013
    Location
    Spokane, Washington
    MS-Off Ver
    Excel 2010 and 2011
    Posts
    4

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

    Your formula worked perfectly! Thank you!

  4. #4
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

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

    Quote Originally Posted by JAllred555 View Post
    Your formula worked perfectly! Thank you!
    You are welcome.

+ 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. Replies: 4
    Last Post: 05-10-2013, 02:45 AM
  2. Conditional Formatting Training Matrix Dates
    By hamiltg02 in forum Excel General
    Replies: 2
    Last Post: 05-01-2013, 05:49 PM
  3. [SOLVED] How to find the dates of the current week
    By uvaidya in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-26-2012, 08:47 AM
  4. Replies: 0
    Last Post: 11-02-2011, 08:47 PM
  5. Excel 2007 : Training Tracker and Due Dates
    By InNeedoHelp in forum Excel General
    Replies: 2
    Last Post: 10-27-2009, 02:52 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