I need to read the date portion of a Dymamic field as a date.

[USAMax]

I have a field that I import with a date. Here is some sample data:
2012-11-05 rev. 003
2012-11-01 rev. 002
2012-11-09 rev. 009
2012-11-12 rev. 021
2012-11-20 rev. 022
2012-11-20 rev. 022
2012-11-17 rev. 005

I need to keep this field as it is and be able to report number of values that are older than 7 days so I created another column CA that reformats this data so it looks like this:
11/05/2012
11/01/2012
11/09/2012
11/12/2012
11/20/2012
11/20/2012
11/17/2012

The problem is that although these look like dated they are only text so this formula =COUNTIF(CA:CA,"-(=TODAY())>7") does not work.

I am good at VBA but I would rather keep this on the sheet. Any ideas?

 

[njimack]

You can retrieve actual dates using the below formula:
=DATE(LEFT(A1,4),MID(A1,6,2),MID(A1,9,2))

 

[USAMax]

That is perfect, the dated are now showing up as dates and I can verify it by changing the date format for the field. THANK YOU

My COUNTIF formula is still not showing dates in this field that are older than 7 days. =COUNTIF(CA:CA,"-(=TODAY())>7")

Can you help with this?

 

[njimack]

Not quite sure which dates you're trying to count. This will count any that are greater than TODAY()-7
=COUNTIF(CA:CA,">"&TODAY()-7)

 

[USAMax]

Not sure I get the quotes around the > but it worked!

Thank you so much and I wish you a very happy Thanksgiving!

 

[njimack]

Not sure I get the quotes around the > but it worked!

Thank you so much and I wish you a very happy Thanksgiving!

While that doesn't apply to those of us outside the US, thanks anyway!!