Excel 2007 Date formatting - countifs

**thrawn86** · 03-01-2011, 03:21 PM

Odd date formatting issue here. I know I've done similar lookups before, but not in 2007.

Column K is a direct copy of 7 dates from a database I have. Column L contains the same values I *manually* typed in. Both are clear of any formulas and are formatted as "short date".

Using:

COUNTIF(K94:K100,">=1/1/2010") and
COUNTIF(L94:L100,">=1/1/2010")

one column returns the right count....the other does not. I'm completely baffled. if I manually edit each individual cell in column K (ie: f2, enter, f2, enter, f2, enter) the cells reformat themselves and the count is correct.

Thanks in advance for any insight.

**NBVC** · 03-01-2011, 03:49 PM

It would be best to see an actual spreadsheet example to best diagnose the problem... but perhaps the K column is being pasted in as text. If you select the column and go to Data|Text to Columns and click Finish, does it work? If you are copying items that are actually text (that may look like numbers) into cells, they will remain text until manually or pro grammatically fixed.

**thrawn86** · 03-01-2011, 03:57 PM

brilliant, thanks that did it. I'm still unsure as to what "text to columns" does but thats exactly it.

of course, it kinda screws up the fact that I wanted this summary sheet to function without any user input...now every time the database spits out a new spreadsheet someones going to have to go in and click "text to columns".

**NBVC** · 03-01-2011, 04:03 PM

You can record a macro and assign it to a button on your sheet, so they just have to press that button.

After recording the macro, edit it, and replace the selection.TextToColumns at the beginning to specify the actual range or column (it has to 1-dimensional).. e.g.

Range("K:K").TextToColumns this will "reformat column K" upon clicking the button.

BTW.. if you go through the TEXT-TO-COLUMNs dialogue and review each window, then what you are doing when clicking Finish, is accepting all the defaults... the important one here being in the 3rd window and the General default for the data column format area... this changes Text to General format.