+ Reply to Thread
Results 1 to 3 of 3

Looking to calculate the number of dates in a column that are current

  1. #1
    Registered User
    Join Date
    07-07-2016
    Location
    St. John's, Newfoundland
    MS-Off Ver
    Excel 2010
    Posts
    1

    Question Looking to calculate the number of dates in a column that are current

    We keep a spreadsheet at work to monitor our staffs health and safety training.

    Each staff member has a column listing the dates they've completed training.

    Topic Provider Priority Expiry Staff Name Date
    First Aid St. John Ambulance 2 1095 Richard 12-Jun-2015
    WHMIS Elearn 1 365 Richard 1-Jan-2014
    Office Safety Awareness Elearn 1 365 Richard 2-Jul-2016

    At the bottom of each persons column, I would like to calculate the number of dates that have not yet expired.

    Not all dates expire in the same amount of time, some are 365 days, 730, 1095, and 1430.

    I started with a basic countif formula;

    =COUNTIF(G14:G116,"<365" &TODAY())

    But that will only help if the dates expire in 365 Days, and are consecutive in the column (which they're not)

    How can I extend this formula to include all the criteria I need?

    HELP!

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.84 (24041420))
    Posts
    8,751

    Re: Looking to calculate the number of dates in a column that are current

    if you add a helper column ,you could calc the expiry date and then countif() would work OK
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Looking to calculate the number of dates in a column that are current

    If I understand what you're wanting...

    Expiry in column D
    Dates in column F

    =SUMPRODUCT(--(TODAY()>D2:D4+F2:F4))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ 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. Calculate number of days between 2 dates & then assign a number based on the answer
    By MrHappyGoLucky12 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-27-2014, 09:20 PM
  2. Replies: 4
    Last Post: 10-15-2012, 07:38 AM
  3. Replies: 0
    Last Post: 06-23-2012, 01:38 AM
  4. Replies: 5
    Last Post: 08-02-2011, 07:11 AM
  5. Replies: 0
    Last Post: 04-15-2011, 11:06 AM
  6. Replies: 6
    Last Post: 05-15-2009, 12:40 PM
  7. Calculate number of days in a column of dates
    By Barbara in forum Excel General
    Replies: 3
    Last Post: 05-25-2005, 11:21 AM
  8. Calculate number of days in a column of dates
    By Barbara in forum Excel General
    Replies: 8
    Last Post: 05-25-2005, 09:55 AM

Tags for this Thread

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