+ Reply to Thread
Results 1 to 11 of 11

Formula that counts dates that are between 60 days and 30 days before todays date

  1. #1
    Forum Contributor
    Join Date
    06-11-2008
    MS-Off Ver
    2003
    Posts
    220

    Formula that counts dates that are between 60 days and 30 days before todays date

    Hi guys,

    I have a load of dates in column k and i am trying to come up with a formula in one cell which allows me to count the number of dates that are between 30 and 60 days before todays date then in another cell using the same range count the number of dates that are between 90 and 120 days before todays date?

    Appreciate any help on the formula,

    Thanks.

  2. #2
    Registered User
    Join Date
    02-14-2013
    Location
    South Carolina
    MS-Off Ver
    Excel 2007
    Posts
    44

    Re: Formula that counts dates that are between 60 days and 30 days before todays date

    Try this. Change A:A to the column that your dates are in:

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    06-11-2008
    MS-Off Ver
    2003
    Posts
    220

    Re: Formula that counts dates that are between 60 days and 30 days before todays date

    Thanks for your help with this. I dont appear to have got this correct however. I have attached my workings. Can you show me where I have gone wrong.

    Many thanks!!
    Attached Files Attached Files

  4. #4
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Formula that counts dates that are between 60 days and 30 days before todays date

    It seems your dates are in the future and what you 're looking for is number of dates between 30 and 60 days in the future?

    If so, tweak kbryant's formula to

    =COUNTIFS(A:A,">="&TODAY()+30,A:A,"<="&TODAY()+60)

    and

    =COUNTIFS(A:A,">="&TODAY()+90,A:A,"<="&TODAY()+120)
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  5. #5
    Forum Contributor
    Join Date
    06-11-2008
    MS-Off Ver
    2003
    Posts
    220

    Re: Formula that counts dates that are between 60 days and 30 days before todays date

    Great, thats a big help. Thank you.

    Im looking to advance the formula now. I have added another column with names and still using the same formula as you have given me i am looking to sum the contracts for each of the 3 names listed in my table and then count how many of them are 30-60 and 90-120.

    thanks again
    Attached Files Attached Files

  6. #6
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Formula that counts dates that are between 60 days and 30 days before todays date

    In Col I
    =COUNTIFS($B:$B,">="&TODAY()+30,$B:$B,"<="&TODAY()+60,$D:$D,I$2)

    In Col J
    =COUNTIFS($B:$B,">="&TODAY()+90,$B:$B,"<="&TODAY()+120,$D:$D,I$2)

    Copy both and paste across

  7. #7
    Forum Contributor
    Join Date
    06-11-2008
    MS-Off Ver
    2003
    Posts
    220

    Re: Formula that counts dates that are between 60 days and 30 days before todays date

    Thanks for your help this evening, much appreciated!!

  8. #8
    Forum Contributor
    Join Date
    06-11-2008
    MS-Off Ver
    2003
    Posts
    220

    Re: Formula that counts dates that are between 60 days and 30 days before todays date

    Since last night i have switched back to my work computer which i use Excel 2003. The formulas above in this thread do not work now. Any suggestions?

    Many thanks,

  9. #9
    Forum Contributor
    Join Date
    06-11-2008
    MS-Off Ver
    2003
    Posts
    220

    Re: Formula that counts dates that are between 60 days and 30 days before todays date

    I have since found out that COUNTIFS do not work on 2003 excel. Is there a different formula i can use instead of this?

    The current formula i have is - =COUNTIFS(A:A,">="&TODAY()+30,A:A,"<="&TODAY()+60)

    Many thanks.

  10. #10
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Formula that counts dates that are between 60 days and 30 days before todays date

    Use

    =SUMProduct((A1:A100>=TODAY()+30)*(A1:A100<=TODAY()+60))

    avoid using full column references with SUMPRODUCT

  11. #11
    Registered User
    Join Date
    01-27-2014
    Location
    Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Formula that counts dates that are between 60 days and 30 days before todays date

    Hello all,

    Sorry to post here, because I think still relates to the question. I have price level from level 1 to level 5, but differentiate by lead days, as example: Group A1 for 0-30 days from today consist of price level 1, price level 2, price level 3, price level 4, price level 5, Group A2 for 31-60 days consist of price level 1, price level 2, price level 3, price level 4, price level 5, And Group A3 for more than 61 days from today consist of price level 1, price level 2, price level 3, price level 4, price level 5. I have to set based on lead days and price level is based on demand, like 0-30% will be based on level1, 31-40% based on level 2, etc to level 5.
    So far I am trying to use below formula but does not work, sorry I am just new to excel and I tried to several formulas but still not working..:'(
    =IF($A1>TODAY()+60,(IF($C1="LEVEL1",'61MR-IDR'!E$10,IF($C1="LEVEL2",'61MR-IDR'!E$9,IF($C1="LEVEL3",'61MR-IDR'!E$8,IF($C1="LEVEL4",'61MR-IDR'!E$7,'61MR-IDR'!E$6))))),IF(AND($A1>TODAY()+30,$A1<=TODAY()+60),IF($C1="LEVEL2",'31MR-IDR'!E$9,IF($C1="LEVEL3",'31MR-IDR'!E$8,IF($C1="LEVEL4",'31MR-IDR'!E$7,'31MR-IDR'!E$6)))))*(IF($C1="LEVEL2",'0MR-IDR'!E$9,IF($C1="LEVEL3",'0MR-IDR'!E$8,IF($C1="LEVEL4",'0MR-IDR'!E$7,'0MR-IDR'!E$6))))

+ 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: 8
    Last Post: 02-08-2013, 04:29 AM
  2. excel formula: days remaining=end date-todays date+extention days
    By fsprings in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 01-04-2013, 06:45 AM
  3. days, months and years between todays date and a series of dates
    By jonathanpalmer in forum Excel General
    Replies: 4
    Last Post: 09-22-2010, 03:04 AM
  4. Todays date + 28 days + 2 week days
    By tubbsy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-18-2008, 12:39 PM
  5. Number of days between 2 dates, if end date is blank assume todays
    By JulesM in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 03-01-2006, 08:45 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