Forum Discussion
Help Needed: Excel 2019 Formula to count date ranges
I am trying to chart quantities of "eligible upgrade dates" based on a column of dates, some of which are in the past, in groups of: "Eligible now" (Today or any date BEFORE today), Within the next 30 days, 30 - 90 days, 90 - 180 days, and beyond 180 days. I only need a single count of each category, from which I can create a simple chart. I have been trying to get COUNTIFS to work, but it does not seem to want to cooperate. I'm sure I am missing something small, but just cannot pinpoint it. Hoping someone on here can assist. Sample file is located HERE: https://1drv.ms/x/s!ArW-WPwPu2B3ga5NoTcRrVygS0_KTQ?e=mhamEx
The formulas I have tried are:
=COUNTIF($A$2:$A$3500,"<=TODAY()")
=COUNTIFS($A$2:$A$3500,">=TODAY()",$A$2:$A$3500,"<=&TODAY()+30")
=COUNTIFS($A$2:$A$3500,">=&TODAY()+31",$A$2:$A$3500,"<=&TODAY()+90")
=COUNTIFS($A$2:$A$3500,">=&TODAY()+91",$A$2:$A$3500,"<=&TODAY()+180")
=COUNTIF($A$2:$A$3500,">=&TODAY()+181")
I have also tried entering todays date in a separate cell using "=TODAY()" and then referencing that cell instead of the TODAY function in the formula, but still get the same failed result.
=COUNTIF($A$2:$A$3500,"<="&H2)
=COUNTIFS($A$2:$A$3500,">="&H2,$A$2:$A$3500,"<="&H2+30)
=COUNTIFS($A$2:$A$3500,">="&H2+31,$A$2:$A$3500,"<="&H2+90)
=COUNTIFS($A$2:$A$3500,">="&H2+91,$A$2:$A$3500,"<="&H2+180)
=COUNTIF($A$2:$A$3500,"<="&H2+181)
Thanks in advance,
Cigar Jerry
4 Replies
I don't see an attachment.
If you don't see an area with "Drag and drop here or browse files to attach" below the area where you compose a post, you can make a sample workbook available through OneDrive, Google Drive, Dropbox or similar, and post a link to the uploaded and shared workbook.
- https://1drv.ms/x/s!ArW-WPwPu2B3ga5NoTcRrVygS0_KTQ?e=mhamEx
Thanks. The formulas are OK.
The values from A4 down look like dates, but they are text values. Could they have been copied from another source?
To remedy this, select all 'dates' from A4 down.
On the Data tab of the ribbon, click Text to Columns.
Click 'Next > ' twice.
Select Date, and select MDY from the drop down next to it.
Click Finish.
You should now have 'real' dates, and the formulas should work.
