Some cells work with formula, some do not, and I can't figure out why

[KGIL789]

Hi everyone!

I am making a tracking database for work for monthly/annual reporting and for the life of me cannot figure out why some cells get counted and some do not. They are written the same way - the only thing that changes is what sheet is being referred to and what date range is being referred to.

The formula I'm using is this: =COUNTIFS(Wells!$AA$3:$AA$2002,">=1/1/2017",Wells!$AA$3:$AA$2002,"<=1/31/2017")

That doesn't count for some reason.

BUT this one does? =COUNTIFS(Wells!$AC$3:$AC$2002,">=1/1/2017",Wells!$AC$3:$AC$2002,"<=1/31/2017")

One column has a date entered when a well permit is issued (non working one). The other is when we pull the sample (this one gets counted). This is set up nearly the exact same way for all our departments (there's a column for address for the environmental dept but not for vital records, etc).

I've changed all the cell formats to both short date and long date as well as text or general and nothing changes. I'm entering today's date to test (11/20/2017). All these cells are hand entered, there are no formulas apart from the counting. Nothing was counted when I used Excel's DATE function either.

Help, please? I have Office 2016.

 

[shg]

Those two formulas are identical.

I've changed all the cell formats to both short date and long date as well as text or general and nothing changes.

You mean the appearnce doesn't change when you change the formatting? if so, then the cells are text, not dates.

Format the cells are general, select the column, do Data > Text to columns, Finish. Now they should like like numbers.

Now change to the format you want and proceed.

 

[KGIL789]

Those two formulas are identical.


You mean the appearnce doesn't change when you change the formatting? if so, then the cells are text, not dates.

Format the cells are general, select the column, do Data > Text to columns, Finish. Now they should like like numbers.

Now change to the format you want and proceed.

The appearance changes, but nothing is counted still.

 

[shg]

Post some example data?

 

[KGIL789]

Some screenshots - all data will be entered in on the color-coded sheets. The non-colorful sheets will be generated based off the colorful sheets.

Images hosted here:

https://ibb.co/dfvua6
https://ibb.co/dcWNTR
https://ibb.co/eahwoR
https://ibb.co/b2vGoR

 

[KGIL789]

This database will be implemented either late 2017 or start of 2018 - right now there's apparently no tracking going on, which is what I've been asked to fix. I've been entering in test data of random dates that should show up but aren't, there's no actual real data that I'm working with.

 

[bellman101]

Hi KGIL,

Can you post a screenshot where we can see a populated date?

What happens if you put an autofilter across row 2? what is in the dropdown for Column AA?

Cheers
JB

 

[KGIL789]

Hi KGIL,

Can you post a screenshot where we can see a populated date?

What happens if you put an autofilter across row 2? what is in the dropdown for Column AA?

Cheers
JB

I'm not sure why I would filter row 2 - in several sheets that's my headers. If I filter a column that has a date in it: https://ibb.co/fqvfHm

AA doesn't have drop downs in any of my sheets.

Some of my columns do have drop downs - "routine", "complaint", (other types of inspections), and for birth/death it's "male" v "female". See here: https://ibb.co/jOchV6 and also here https://ibb.co/dN0i3R

For wells/septics there is no drop down menu. Here are all the columns for wells with fake data entered in row 1: https://ibb.co/gPMsV6

 

[bellman101]

No, I'm not suggesting you use autofilter as a feature of your file. Just that you temporarily apply one, and then look in the autofilter drop down list for column AA. if they are being seen as dates, then it should give years, which expand to months, and then days. If it does not do this, then for some reason they are not seen as dates. Also, in one of your screen shots above you were looking for dates <= 31st Feb, which I think will resolve in an error. Try < 1st March instead...

 

[KGIL789]

No, I'm not suggesting you use autofilter as a feature of your file. Just that you temporarily apply one, and then look in the autofilter drop down list for column AA. if they are being seen as dates, then it should give years, which expand to months, and then days. If it does not do this, then for some reason they are not seen as dates. Also, in one of your screen shots above you were looking for dates <= 31st Feb, which I think will resolve in an error. Try < 1st March instead...

The columns that should have dates are all appearing with year, then month, then day when I apply the filter.