Forum Discussion

Copper Contributor

Jun 27, 2022

Count an Excel Cell with a Date

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 ho...

Copper Contributor

Jun 27, 2022

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!

MVP

Jun 27, 2022

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
Copper Contributor
Jun 27, 2022
HansVogelaar 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?
- skobuffs
  Copper Contributor
  Jun 27, 2022
  HansVogelaar 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...
  - HansVogelaar
    MVP
    Jun 27, 2022
    skobuffs
    
    You must always enter a date according to your system date settings, regardless of the date format of the cell.
- HansVogelaar
  MVP
  Jun 27, 2022
  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.