Converting from DD-MM-YYYY to YYYY only in excel
Lately, I needed to go through a column full of dates – in the format MM-DD-YYYY. The thing is , I didn’t care for the day or the month of the data. All I needed was the YEAR of the date.
Here is a simple trick – just create a new column in excel – which contains a formula to display only the YYYY part of MM-DD-YYYY
- Add a column to the Excel sheet. Let’s say the dates of birth are in B and down.
- In another column, enter YearDate in row 1 and the formula =TEXT(B,”YYYY”) in row 2. Copy and Paste down from row 2 as far as necessary.
Summary
There may be other ways to accomplish the same thing. This seems to be pretty quick and easy – to get from complete date values to just YYYY (year only) in excel.
Anuj holds professional certifications in Google Cloud, AWS as well as certifications in Docker and App Performance Tools such as New Relic. He specializes in Cloud Security, Data Encryption and Container Technologies.
Anuj Varma – who has written 1217 posts on Anuj Varma, Hands-On Technology Architect, Clean Air Activist.
Works like a charm. Thanks for this. At the end Excle autocorrected this to: =TEXT([@helpcolumn],”YYYY”). I used “helpcolumn” because I did not want to corrupt the data and copied it, but you get the gist of it.
It works, Great!
Thank you very much for this useful tip.