Count an Excel Cell with a Date

[skobuffs]

Hello,

I have a sheet that has a list of 182 people that can buy a permit.  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.  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.

=10-COUNTIF(H6:H188,">=TODAY()")

My thinking was that any permit that is less than today would be expired and I wouldn't want it added to the total.  For some reason this isn't working.  Any ideas?

Thanks,

Marco

[#2]

@skobuffs 

Use

=10-COUNTIF(H6:H188,">="&TODAY())

[#3]

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!

[#4]

@skobuffs 

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.

[#5]

@Hans Vogelaar 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?

[#6]

@skobuffs 

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.

[#7]

@Hans Vogelaar Just figured it out. When I put in 6/30/22 it isn't reading that as a date. I have to put 30/6/22 and then it works. That's weird. I thought formatting that whole column as date would make the date a date with the format MMDDYY no matter how it was entered...

[#8]

@skobuffs 

You must always enter a date according to your system date settings, regardless of the date format of the cell.

[#9]

@Hans Vogelaar Understood. One last question. If I make the spreadsheet with my system settings as DDMMYY, upload the spreadsheet to Teams, and someone else on my team uses MMDDYY to enter the date, will that work (assuming that their computer is MMDDYY)?

[#10]

@skobuffs 

If your workbook will be used by people that might have different system settings, I'd either use an unambiguous date format such as d-mmm-yyyy (7-Jun-2022) or the ISO format yyyy-mm-dd (2022-07-06), or else one of the two date formats marked with an asterisk in the Format Cells dialog:

Excel will automatically adjust those two formats to the user's system settings. So I see 2022-06-07, someone in the UK sees 07/06/2022 and someone in the USA sees 06/07/2022.

[#11]

That makes sense. Thank you very much!!