Excel Formula - Count Dates from another Sheet

[RobExcels]

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

 

[Becky]

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!

 

[RobExcels]

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.

 

[Becky]

Hmmm that's odd. Are the date cells formatted as dates (ie not text)?

 

[RobExcels]

Hmmm that's odd. Are the date cells formatted as dates (ie not text)?

Yes. They are formatted as dates: 4/30/2017

 

[Becky]

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?

 

[Becky]

Also, for completeness, check that the formatting of the cell in which each formula is is set to number.

 

[RobExcels]

Yes I have checked both of these. Formats of cells are correct and the automatic option is selected. it still reads 0

 

[Becky]

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.