Convert Year only into 01/01/YYYY

BradleyS

Active Member
Joined
Oct 28, 2006
Messages
361
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
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.
 
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
 
Upvote 0
Spot on Barry.

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

Really appriciated.
 
Upvote 0
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?
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top