Count an Excel Cell with a Date

Question

Hello,&nbsp;I have a sheet that has a list of 182 people that can buy a permit.&nbsp; I only want 10 active permits at any given time, so I want to set a counter at the top of the spreadsheet that shows how many permits are available.&nbsp; Since the permits will expire, though on different dates, I thought I'd use the COUNTIF function where the H column has the expiration date of the permit.&nbsp;=10-COUNTIF(H6:H188,"&gt;=TODAY()")&nbsp;My thinking was that any permit that is less than today would be expired and I wouldn't want it added to the total.&nbsp; For some reason this isn't working.&nbsp; Any ideas?&nbsp;Thanks,Marco&nbsp;

hansvogelaar · Answer

skobuffs&nbsp;
Use
&nbsp;
=10-COUNTIF(H6:H188,"&gt;="&amp;TODAY())

skobuffs · Answer

I tried that one and it isn't working. I also made sure to format the H column as short date. Should I maybe use another format? Thanks for helping!

hansvogelaar · Answer

skobuffs&nbsp;
Make sure that you format the cell with the formula as General.
In the example below, there are 4 dates in the future, so there are 10-4 = 6 available.

skobuffs · Answer

HansVogelaar&nbsp;Okay, I had the cell with the formula (I2) formatted as Date. Changed it to General. I left the H column formatted as date, but it's still not counting properly. Any other thoughts?&nbsp;&nbsp;

hansvogelaar · Answer

skobuffs&nbsp;
I notice that the value 6/30/22 is left-aligned - that might indicate it's a text value. What happens if you select that cell, press F2 then Enter?
If that doesn't help:
Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.

Forum Discussion

Count an Excel Cell with a Date

Share

Resources