The date is formatted to MDY (mm/dd/yyyy). This is a format used only in the US and a few smaller countries. The first way, you can convert date is to use the Format Cells window. In order to do it, click cell B3, then click the right mouse button and select the Format Cells option.

• Convert Dates In Excel
• Convert Dates In Excel To Calendar
• Convert Dates In Excel To Month
• Convert Date In Excel To Day Of Week

Enter your due dates in column A. Enter the number of days to add or subtract in column B. You can enter a negative number to subtract days from your start date, and a positive number to add to your date. In cell C2, enter =A2+B2, and copy down as needed. How to convert a number to date. There is no DATE type in Excel. Dates are numbers formatted to dates. The following example has numbers in column A, and the same numbers formatted as dates in column B. Increasing the number by 1, increases the date by one day, starting from 1/1/1900.

If your spreadsheet contains text representations of dates, rather than actual dates (stored internally as integers by Excel), this may produce errors when you try to use these dates in calculations. Therefore, this page describes three different methods that you can use to convert text to a date in Excel.

Convert Text to a Date Using Excel Error Checking

Excel has an error checking option that can alert you to the presence of cells containing text representations of dates with two-digit years. This will be seen as a small colored triangle (the error indicator) in the top left corner of your cells (see below).

If your cells display this error indicator, you can use Excel error checking to convert text representations of dates into actual numeric dates.

To do this:

1. Select the cell (or cells) containing the values that you want to convert.

   This will cause a warning symbol to pop up at the side of the cell(s). If you then hover over the warning symbol, a warning message is displayed (see below).

2. Click on the warning symbol, to bring up the Error Checking menu (see rightabove).

   Select one of the options

   Convert XX to 19XX

   or

   Convert XX to 20XX

   to convert the cell values into dates.

In order for the above method to work, you need to ensure that you have the Excel Error Checking option for cells containing years represented as 2 digits enabled.

To access this option:

1. Click on the File tab (or the Microsoft Office Button in Excel 2007) on the Excel ribbon, and select Options.
2. Within the Excel Options window, select Formulas.
3. Within the Error Checking section, make sure the option Enable background error checking is checked.
4. Within the Error checking rules section, make sure the option Cells containing years represented as 2 digits is checked.
5. Click OK.

Convert Text to a Date Using Excel Text to Columns

The Excel Text to Columns command will also convert Excel text to dates. This method has the advantage that it can recognise several different date formats. However, the Text to Columns command will only work on one column at a time.

To use Excel Text to Columns to convert text to dates:

1. Select the range of cell(s) that you want to convert (this must not span more than one column).

2. From the Data tab on the Excel ribbon, select the Text to Columns option (see rightabove).

   This will cause the Convert Text to Columns wizard to pop up. Within this:

   • Make sure the Delimited option is selected and click Next >;
   • Make sure that none of the Delimiters are selected and then click Next > again;

   • You should now be offered a selection of Column Data Formats. Select Date.

   • From the drop down menu alongside the Date option, select the format that applies to the dates in your spreadsheet (see rightabove).
   • Click the Finish button.

Convert Text to a Date Using the Excel Datevalue Function

The Excel Datevalue function converts a text representation of a date into an Excel date serial number.

Note that, after using the function to convert a text string into a date, you need to make sure that the cell containing the function is formatted as a date.

Datevalue Function Examples

Cells A1-A3 of the spreadsheets below contain three different text representations of the date 01 January 2016.

The Excel Datevalue function is used in column B of the spreadsheet, to convert these text values into date serial numbers.

In the above 'results' spreadsheet, column B is formatted with the 'General' format type. Therefore, the date serial number 42370 is displayed as an integer.

If you want to display the contents of column B as dates, you need to change the formatting of these cells to have a date format.

The easiest way to do this is to select the cell(s) to be formatted and then select the Date cell formatting option from the drop-down menu in the 'Number' group on the Home tab of the Excel ribbon (see below):

The resulting spreadsheet, with the cells in column B formatted as dates, is shown below:

Further methods of formatting dates in Excel are discussed on the Excel Date Format page.

Convert Dates In Excel

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.

Convert Dates In Excel To Calendar

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.

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 Month

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.

Convert Date In Excel To Day Of Week

— 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.