Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Dealing with dates in multiple formats (and converting them to TEXT)


Hello, I have built a workflow in Excel that needs to process a column of dates that are often in multiple formats and extract only the year from the end of these multiple formats for further calculation. I've not made use of VBA for this section either. The problem is that Excel keeps instantly recognising the dates and formatting them as dates, so that when I try to extract them as text (or values) in another column, they show up as unintelligible numbers. Sometimes Excel does not do this, and the workflow is successful.

Can anyone suggest a way to extract the entire column simply as the TEXT so I can use LEFT and RIGHT functions to extract the year?





Suggest (for a date value in cell A2 say) you try:

That should always return the year (as a 4 digit number). If you really need to convert that number to text, then use:
=TRIM(" " & YEAR(A2))
(where I've added a space to make it text then trimmed that off- seems to work!).

You may find it useful to see Don's tutorials in the sub-section Date and Time Calculations in Excel



Didn't that work for you sonehow? 
John_Ru (rep: 4022) Dec 23, '21 at 8:51 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login