Excel countifs and dates

Question

I've developed a small Excel presentation sheet, which uses some countifs formulas to look through rawdata that the users will be pasting into another sheet.

My problem is that one of my countifs criteria is a date. I simply need to test if a startup date has been entered for the given entry. In my countifs formula i'm using:

Rawdata!G:G;">10/1/2000"

This however doesn't count all users eventhough they have a startup date in G column.

The problem seems to arise when an entry's date looks like the top one in 1. (it's left adjusted)

If a edit the top date in 1 and manually put in 30-10-2014, the date will right align and the entry will be counted. This is not an option for a 1000+ lines data paste :)

I've made sure that the cells in the column G are formatted as dates (right clicking and format cells as), but it still seems as if some are different than the other.

What I'm missing in making Excel understand that all values in G are dates?

Answer 1

When a cell stores date as text (usually alligned to left), changing cell format won't help. There are numerous ways to fix it:

• select column with wrong formatted dates, choose Data->Text to columns and in the last step select Date and in your case DMY
• another option is to enter 1 in any cell, copy this cell, select range with dates, choose Paste special->Multiply, format range as dates.