Count training complete and incomplete by referencing dates

[tcormack]

Hi

I have a training report and I'm trying to figure out a way to quickly count the number of training courses complete, and the number outstanding. If the course has been taken and is required, there's a date populated within the spreadsheet. If the course is due to be renewed and has been scheduled there is an SD date in some cells but I appreciate this may throw the report off by having text and date together so that can be removed if required.

I tried conditional formatting on all dates and highlighting those <today(), however I couldn't figure out how to then count the highlighted cells and those not highlighted.

I've attached some sample data in the hope that someone is able to assist.

Thanks!

 

[Fluff]

Hi & welcome to MrExcel.
How bout something like
=COUNTIF(H3:XL3,"<"&TODAY())

 

[tcormack]

Hi

Thanks for the response, however, it's not working. I can see dates that have expired however the count remains at 0. Is there any date formatting that might be preventing this from working?

 

[Fluff]

Make sure that the dates are real dates not text.
Select H3:XL3 & change the format to "General" do you see numbers like 43850

 

[tcormack]

No, all dates remain when I select "General"

 

[Fluff]

In that case your dates are text, not real dates. You will need to convert them to real dates.

 

[tcormack]

Is there a quick way to convert dates stored as text, to real dates? There are 784 dates in my worksheet, the dates appear as 20/08/2012.
I could use the VALUE formula but think it will take such a long time I wondered if there was a better way?

 

[Fluff]

You can use Text to Columns on the data tab.
Delimited > next > uncheck all boxes > next > select DMY from the date dropdown > Finish