Convert Year only into 01/01/YYYY

[BradleyS]

I have lots of dates that are not entered correctly in a single column of data and are messing up my formulas, instead of having to trawl through hundreds of these dates, is it possible to automatically convert them to 01/01 and show the actual year.

For example:
1951 should be 01/01/1951
1922 should be 01/01/1922

I tried conditional formatting but couldn't get it to work.

Any suggestions would be most appriciated.

Thank you.

 

[texasalynn]

sounds like the cell is formatted as the yyyy only. Look at that to confirm

 

[barry houdini]

If you have a mixture of real dates and some with only years then you could try this formula in the next column to convert to dates

=IF(A2<4000,DATE(A2,1,1),A2)

Put that in B2 and copy down

 

[BradleyS]

Spot on Barry.

This has saved me hours of work, thank you very much.

Really appriciated.

 

[Ron Coderre]

A little clunky...but, maybe this:

•Enter 0.1 in a blank cell
•Copy that cell
•Select the range of years
•Edit.Paste_Special.Add...click: OK
•Edit.Replace
...Find What: . (that's a decimal point)
...Replace with: - (that's a dash)
...Click: Replace All
•Data.Text_to_Columns
...Click: Next
...Click: Next
...Check: Date....YMD
...Click: Finish

All year values will be converted to Jan-01 of the year.

Is that something you can work with?