Determine if Cell Format is M/D/YYYY and Change to Numeric Value in YYYYMMDD Format

Hi,

I have data files that come in with dates formatted in a variety of ways. Some columns have dates in a YYYY-MM-DD format, and those can be converted into a number with a YYYYMMDD format just by replacing the dashes in the cells with nothing. However, other columns can sometimes come in with dates in a M/D/YYYY format, and these need to be modified to also be in a YYYYMMDD format. How can I write VBA code to evaluate whether the date is in a M/D/YYYY format or not, and if so, how to convert it to YYYYMMDD format?

BTW, I can't just apply a YYYYMMDD format to all cells because that formats the cells that are now numbers as all #######s.

Any help that can be suggested will be greatly appreciated!

Bryan

Hi, Special-K,

I understand what you're suggesting for the 2nd and 3rd parts of your solution, but wanted to confirm what you meant by "take the text value of the cell." I used a formula to copy the m/d/yyyy formatted cells into a new column, then copied the column and did a paste special-values on it, and finally formatted the entire column as Text. Is that what you were suggesting?