Excel Formula - Count Dates from another Sheet

RobExcels

RobExcels

Joined
Apr 27, 2018
Messages
4
Reaction score
0
Hello,

I need a formula that will count the number of expiration dates on another sheet within the same workbook.
I'd like to show a count of:
1. # Currently expired (Today and earlier)
2. # Expiring within 30 days
3. # Expiring within 60 days
4. # Expiring within 90 days

I've devised the following formula but am stuck: =COUNTIF('SHEET2'!Z2:Z200,"<"&TODAY())

Thanks for any help!
Robert
 
Welcome to the forum! :)

That formula should work for #1 on your list, but I would make one small tweak:

=COUNTIF('SHEET2'!Z2:Z200,"<="&TODAY())

The equals sign means it will include any expiration dates that are today.

To count the number expiring within 30 / 60 / 90 days, use this formula:

=COUNTIF('SHEET2'!Z2:Z200,"<="&(TODAY()+30))

(replace 30 with 60 / 90 as appropriate)

Hope this helps!
 
Thanks!

I appreciate the assist. The formula still isn't producing anything for me. I have the entire column filled with dates but the cell reads that 0 are expired. It should read that several are expired, due with 30 days, 60 and 90 respectively but says 0 for all.
 
Hmmm that's odd. Are the date cells formatted as dates (ie not text)?
 
Ok, if you go to the Formula tab, look at the Calculation section - make sure the Calculation Options are set to Automatic. Click on Calculate Now, does that prompt the formula to update?
 
Also, for completeness, check that the formatting of the cell in which each formula is is set to number.
 
Yes I have checked both of these. Formats of cells are correct and the automatic option is selected. it still reads 0
 
Has the formula ever worked? For example, if you have the formula and data within the same sheet does it work? Have you tried deleting the range and re-linking it?

Sorry for all the simple questions, I imagine it's frustrating for you. When these things happen it's often due to a simple problem, so it's always best to cover those things first.
 
Back
Top