Look Who it Was Random video

Excel - Converting Month Abbreviations to Real Dates in Excel - Excel Tutorial - Episode 507

667 views · Published 9 September 2009 · 1:57 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial - Converting Month Abbreviations to Real Dates in Excel .

Welcome back to the MrExcel podcast! In today's episode, we're tackling a common problem that many Excel users face - converting three letter month abbreviations into actual dates. This can be a frustrating issue, as it prevents us from being able to sort our data correctly. But fear not, because I have a solution that will make your life a whole lot easier.

First, we'll create a new column called "real date" where we will convert the three letter month abbreviations into actual dates. To do this, we'll use the DATEVALUE function. This function takes a date in text format and converts it into a serial number that Excel can recognize as a date. So, we'll start by typing =DATEVALUE(, and then we'll add the number one in quotes, a space, and then the cell reference for the month abbreviation. For example, =DATEVALUE("1 " & C2) will convert "May" into May 1st of the current year. We'll copy this formula down for all the cells in the column and voila! Our three letter month abbreviations are now real dates.

But what if we want to keep the original data in our sheet? No problem! We can use the CONCATENATE function to combine the month abbreviation with the number one in quotes and a space. This will give us the same result as using the ampersand symbol. So, instead of just typing "Jan" in the formula, we'll use ="1 " & C2. This will give us the same result as before, but now we can keep our original data in the sheet.

Thanks for tuning in to today's episode. I hope this tip helps you save time and frustration when working with dates in Excel. Don't forget to subscribe to our channel for more helpful Excel tips and tricks. See you next time for another netcast from MrExcel!"

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/

Table of Contents:
(00:00) Introduction to the problem
(00:21) Issue with three letter month abbreviations in date column
(00:31) Difficulty in sorting dates correctly
(00:41) Solution: Creating a new column with real dates using DATEVALUE function
(01:11) Sorting the data correctly
(01:25) Using concatenation to accurately convert dates
(01:38) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Converting month abbreviations to dates in Excel
Converting month abbreviations to real dates in Excel
Excel tip: Sorting dates with three-letter month abbreviations
Excel tutorial: Converting month abbreviations to real dates
Excel tutorial: Converting month abbreviations to sorted dates
How to change month abbreviations to real dates in Excel
How to sort data with three-letter month abbreviations in Excel
Sorting data with DATEVALUE function in Excel
Sorting data with the DATEVALUE function in Excel
Sorting dates in Excel
Using CONCATENATION in Excel to convert month abbreviations to dates
Using the DATEVALUE function in Excel

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152610/



Your crazy software exported a file where the date column has the not-so-useful values like Jan, Feb, and Dec in a column. Episode 507 looks at a function to convert those values to real dates.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

More from this channel