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

1. ## 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. ## 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. ## 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!!

4. ## 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)

5. ## 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

6. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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))))

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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