How to convert date to different or specific format in Excel?

If you want to convert the standard date to other date formats in Excel, how can you do? Here, I have three methods that can solve this job.

Convert date to different format with Apply Date Format

The most important function for text date conversion in Excel is the DATEVALUE function. It is used to convert textual dates to a number that Excel recognizes as a date. The dates are stored as sequential numbers starting with 1 representing “Jan. The DATEVALUE function converts a date that is stored as text to a serial number that Excel recognizes as a date. The DATEVALUE function is helpful in cases where a worksheet contains dates in a text format that you want to filter, sort, or format as dates, or use in date calculations. Open in Excel the workbook that has the dates you want converted to text. Otherwise, you can.

How To Convert Dates In Excel To Sort

Use DATEVALUE Function To Convert a Text to Date. Using the DATEVALUE function is a basic.

Convert date to different format with Format Cells

In Excel, you can convert date to other date format in the Format Cells function.

1. Select the dates you want to convert, right click to select Format Cells from context menu.

2. In the Format Cells dialog, under Number tab, select Date from Category list, and then select one format you want to convert to from the right section.

3. Click the OK button to apply the new format.

Convert date to different format with formulas

If you want to use formula to convert date to year/month/day only, you can do as below:

Convert date to year =TEXT(A1,'yyyy')

Convert date to month =TEXT(A1,'mm')

Convert date to day =TEXT(A1,'dd')

Convert date to different format with Apply Date Formatting

If you have Kutools for Excel, itsApply Date Formatting function will do you a big favor.

After installing Kutools for Excel, please do as below:（Free Download Kutools for Excel Now!)

1. Select the dates you want to convert format, and click Kutools > Format > Apply Date Formatting.

2. In the popping out dialog, select the date format you need from left pane, and preview in right section.

3. Click Ok or Apply to convert the dates.

Demo

• Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
• Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
• Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
• Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
• Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
• Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
• Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
• Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
• More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

• Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
• Open and create multiple documents in new tabs of the same window, rather than in new windows.
• Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

Dates can be awkward in Excel. The most prevalent format worldwide is Day-Month-Year (DMY), but not all countries follow it. One such country you might have heard of that differs from this “standard” is the US, where it is commonplace to use Month-Day-Year (MDY).

Speaking from personal experience, I remember one project manager was nearly fired after he thought the deadline was 1 March 2015 when it was in fact 3 January 2015. This is the danger of 1/3/15, for example.

To show you how to overcome this problem, I will illustrate with converting US dates to what is often known as the “European” date format. Now, I know many readers would prefer this to be the other way around. I apologise, but I am an Australian Brit with the appropriate regional settings on my machine, and it’s a little awkward to perform screenshots that way. Don’t worry though — just follow me in reverse.

The problem becomes significant when you receive date data in a spreadsheet that is not recognised by your regional settings — or worse, actually is, like my unfortunate project manager mentioned above. For me, my computer cannot make sense of US date formats such as those shown in the screenshot below.

I have left the data in “General” style deliberately so you can see only one entry, cell A4, is recognised as a number (date). The problem is, even that’s wrong as that represents 5 December 2022, not 12 May 2022.

How do I convert it? We could use Power Query / Get & Transform — but that’s not really what this article is about. There is an easy way in Excel — but first, let’s start with a hard way.

In the screenshot below, I have managed to fix the issue:

See? Easy. Oh, sorry, I didn’t display the formula I used to do this in the image. Here it is for cell C2:

Any questions?

I have provided the formula because I am frustrated by the number of times I have read on the internet that this is not possible formulaically. Rubbish. You would just be a little insane to do it that way.

I won't explain this formula. Suffice to say it only works for converting US dates to European dates; the text strings are delimited with “/” and do not contain “@” in the text string. If you want the conversion to go the other way, simply replace d/m/yyin all instances above with m/d/yy.

Having said that, I think we are all agreed we need another — simpler — way. Let’s start again. Back to the original data, I make a copy in cells C2:C11, as shown in the screenshot below.

I do this so I may retain the original data (it’s always best to keep a copy in case you make a mistake). Next, I highlight cells C1:C11(including the header) and click on Text to Columns in the Data Tools grouping of the Data tab of the ribbon (Alt+A+E):

This generates the Convert Text to Columns Wizard dialog box. In Step 1, choose the Delimited option and click Next.

Convert Dates In Excel To Month

This means the data will be split into columns based upon a specified delimiter. Except we are going to cheat and not do that. In Step 2, uncheck all delimiters and then click Next, as shown below:

Now we come to the step that we actually want. We don’t use the Text to Columns feature to split data into separate columns. No, I want Excel to recognise my data as dates.

In this final step, select the Date: option in the Column data format and choose the date format that matches the data as it currently is — not what you want it to be. You are asking Excel to recognise it. In my case, the data is in Month-Day-Year format (MDY), so this is what I selected. Once you have chosen, click Finish.

I think you will agree this is far simpler than the formulaic approach and, more importantly, works for all date scenarios — as long as the original dates are formatted consistently.

Convert Dates In Excel To Text

As you keep working with dates, you will appreciate more and more the need for consistent dates — and the fact that they really aren’t that difficult to manipulate once you know the tricks.

Converting Dates In Excel

— Liam Bastick, FCMA, CGMA, FCA, is director of SumProduct, a global consultancy specialising in Excel training. He is also an Excel MVP (as appointed by Microsoft) and author of Introduction to Financial Modelling. Send ideas for future Excel-related articles to him at liam.bastick@sumproduct.com. To comment on this article or to suggest an idea for another article, contact Jeff Drew, an FM magazine senior editor, at Jeff.Drew@aicpa-cima.com.