+ Reply to Thread
Results 1 to 11 of 11

count number of dates in a column that are coming up within 30 days or have gone by

  1. #1
    Registered User
    Join Date
    02-07-2012
    Location
    Portland, OR
    MS-Off Ver
    Excel 2007
    Posts
    49

    Thumbs up count number of dates in a column that are coming up within 30 days or have gone by

    hi, so here's what i believe to be a simple question, yet it eludes me...

    I will buy an "excel, how to" book soon, but i figured i'd ask this question ahead of time:

    I have a spreadsheet with a list of dates in columns E, F, G.. i need a (1 for each column) formula to check which of those dates are within 30 days of today and tally the number below as well as highlight or change the color on the date in the cell to show which dates are about to expire.. thanks ahead for the help
    Last edited by mrmarchuk; 02-10-2012 at 07:16 PM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: check which dates are within 30 days of today and post total number below

    within 30 days of today
    ... in the past or in the future?

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    02-07-2012
    Location
    Portland, OR
    MS-Off Ver
    Excel 2007
    Posts
    49

    Re: check which dates are within 30 days of today and post total number below

    sorry should have clarified, within 30 days of today, in the future (upcoming expiration dates) or expired dates.


    thank you
    Last edited by mrmarchuk; 02-09-2012 at 06:23 PM.

  4. #4
    Registered User
    Join Date
    02-07-2012
    Location
    Portland, OR
    MS-Off Ver
    Excel 2007
    Posts
    49

    Re: count number of dates in a column that are coming up within 30 days or have gone

    basically, anything that is expired or will expire in 30 days

  5. #5
    Registered User
    Join Date
    02-07-2012
    Location
    Portland, OR
    MS-Off Ver
    Excel 2007
    Posts
    49

    Re: count number of dates in a column that are coming up within 30 days or have gone

    so is this possible?

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: count number of dates in a column that are coming up within 30 days or have gone

    Use Conditional Formatting.

    Select all the cells, for example, E2:G200 and use the CF formula/condition: =E2<=TODAY()+30

    Format as required.


    Regards, TMS

  7. #7
    Registered User
    Join Date
    02-07-2012
    Location
    Portland, OR
    MS-Off Ver
    Excel 2007
    Posts
    49

    Re: count number of dates in a column that are coming up within 30 days or have gone

    awesome thank you, now is there a way to get it to search e2:g200 and tally up how many certificates are/soon to be expired?

    i dont know the commands but basically something along the lines of ( if e2:g200<=today()+30, then count (add 1) ), a simple number would suffice

    thanks for your help TMShucks
    Last edited by mrmarchuk; 02-10-2012 at 12:02 PM.

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: count number of dates in a column that are coming up within 30 days or have gone

    Sounds like that should be:

    =COUNTIF(E2:G200,"<=" & Today()+30)


    Regards, TMS

  9. #9
    Registered User
    Join Date
    02-07-2012
    Location
    Portland, OR
    MS-Off Ver
    Excel 2007
    Posts
    49

    Re: count number of dates in a column that are coming up within 30 days or have gone

    Quote Originally Posted by TMShucks View Post
    Sounds like that should be:

    =COUNTIF(E2:G200,"<=" & Today()+30)


    Regards, TMS
    awesome now what if i want this formula to run on 1 sheet, pulling the data from another, what modification would be required?

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: count number of dates in a column that are coming up within 30 days or have gone

    Good grief ... a little scope creep here!

    Just qualify the range with the sheet name.

    ...(Sheet2!E2:G200, ...)


    Regards, TMS

  11. #11
    Registered User
    Join Date
    02-07-2012
    Location
    Portland, OR
    MS-Off Ver
    Excel 2007
    Posts
    49

    Re: count number of dates in a column that are coming up within 30 days or have gone

    aw shucks.. thank you

+ 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