7
 min read

How Do You Format Dates in Excel?

Excel gives you multiple ways to format dates.

Dates are often a vital field when keeping track of data. You can type in character strings for dates, like "January 5 1979". However, there are many operations we would like to do to dates that are unfit for strings. For example, if you have "January 5 1979" in your data, how do you compute the next day to get "January 6 1979", or the next year "January 5 1980"? How do you ensure all your dates look the same? It would be unprofessional to list dates with mixed formats like "January 5 1979" and "01/05/79". So, dates are not mere text. There must be a way to treat them as objects subject to date math and format them as text. This is why it is useful to know how to format dates.

Excel solves these issues with two concepts:

1) Dates are treated as numbers

  • This helps with date math and making sure all dates have a uniform interpretation.

2) Dates, which are numbers, can have different visual formats.

  • This helps with display issues to make sure all your dates look clean and professional.

Important Note to Format Dates in Excel

The input for a cell can vary from what appears in your spreadsheet. Exporting dates to CSV will export the text that appears in your spreadsheet, not the text that was input to achieve that date. In the example below, the input is a formula "=DATE(2021, 6, 5)", but what appears in the spreadsheet is the date "2021-06-05".


"=DATE(2021, 6, 5)" appears in the input box, and "2021-06-05" appears in the cell.

We will go over how to convert to and from various date formats.

Format Dates as Numbers or Dates

Excel interprets every date starting from January 1st, 1900 as a number that increases by 1 for every passing day. 1 is January 1st, 1900, 366 is January 1st, 1901, and 38582 is August 18th, 2005, etc. You can easily convert between any date and its corresponding serial number by either of the following methods.

Method 1 - Format Dates As Numbers

  • Right-click on a cell containing a date or number (here, we convert 1 in cell A1).
  • Select "Number Format..." from the scroll-down menu. Select "Date" to convert 1 to its corresponding date. Select what date format you want to appear in your cell.

Right click on the cell you want to format, and select "Number Format" from the dropdown menu.
  • Select "Date" to convert 1 to its corresponding date.
  • Select what date format you want to appear in your cell. Click "OK." This option will format dates to look like the template selected.


Several date formats appear when you select the "Date" category from the dropdown menu.
  • Now, your cell will contain the date corresponding to 1 (January 1st, 1900).


The number "1", when formatted as a date, appears as "1900-01-01"
  • You can repeat steps 1 and 2, but then select "Number" to convert any date (from January 1st, 1900 onwards) to its corresponding serial number. This will allow you to format dates as numbers.

When you select the "Number" category, various number formats appear.

Method 2 - Format Dates to Serial Numbers: DATEVALUE function

The DATEVALUE function converts a date in text form to its serial number.


Type =DATEVALUE("date text") into a cell and hit enter. It will now format dates on your spreadsheet as the serial number corresponding to the date. Make sure to include the quotation marks around the date text, and make sure your date text is in an accepted excel date format.

=DATEVALUE("2014-09-22"), when entered in the input box, appears as "41904" in the cell.

How Excel automatically Detects and Formats Dates

Sometimes you don't need to tell Excel to read your input as a date manually.

Some inputs will automatically get formatted as dates.


You can tell when this has happened when the cell changes from Date under the Number Format heading. You can right-click on the cell to see what format it is (using the Number Format method just discussed). Or you can use the Number Format Shortcut in the Toolbar to see if the status changes.

Format Dates Toolbar Shortcut

  • Type in some text. In this case, 08-10-30.

The Format dropdown menu can be found to the right of the "Merge" menu
  • Hit enter and see as the cell automatically changes from General to Date. Note that Excel also changes the date format to YYYY-MM-DD.

"Date" is one of the options for the format dropdown menu.

Format Dates as Text

You can use the TEXT function to convert serial date numbers to text.

The TEXT function takes in a number and a format and outputs text corresponding to that date in that format.


  • Type =TEXT(serial number,"date format") into a cell. Plug the serial date number into the first position of the formula.
  • Plug in a date format. Make sure to include the quotation marks around it. You have a wide array of date formats to choose from for your text date.

"=TEXT(41904,"MMM/DD/YYY")", when put in the input box, appears as "Sep/22/2014" in the cell.

Choosing How To Format Dates

Using the TEXT function has many date format options to convert a serial date number to text. Here is a step-by-step guide to choosing your format.

  • Choose whether you want to include the month, the day or the year (or any combination thereof). You can even include the weekday by using "DDDD".
  • Choose what format you want the month, date or year to take and use the corresponding code format.
  • Put the month, day and year in the order you want.
  • Choose the punctuation you want to use (slash, comma, period, etc).

E.g. "MMM/DD/YYYY" comes out Sep/22/2014 (like in the example above).

E.g., "MM.DD.YY" comes out 09.22.14.

E.g. "M,YYYY" comes out 9,2014.

Format Dates With Different Formats

You can also plug a DATEVALUE function into the first position of a TEXT function (instead of a serial date number) since a DATEVALUE function corresponds to a serial date number.

Using the TEXT and DATEVALUE functions together allows you to convert a date in one format to another format.


Below is an example where I plug a DATEVALUE function into the serial date number slot.


"=TEXT(DATEVALUE("2014-09-22"),"MMM/DD/YYYY")", when put in the input box appears as "Sep/22/2014".

This code allows me to convert between date formats by first converting one date text to its corresponding serial number.

International Conventions To Format Dates

Countries, regions and companies have different standard practices for date format conventions. These different practices can lead to ambiguity in dates. For example, in much of Europe, it is traditional to use the DD.MM.YYYY format (e.g. 20.04.2020). However, in North America, it is uncommon to place the day before the month. To avoid ambiguity, the international date convention (ISO 8601) uses the YYYY-MM-DD format (e.g., 2020-04-30).

  • Right-click on the date that you want to reformat. Go to the number format menu option.
  • Choose from the list of international date conventions.

The Date format menu has an option to input your location.

What if I want to format dates using only a year?

Suppose you have a column of years, but you need a column of dates in the YYYY-MM-DD format. The DATE function allows you to easily convert those years into the first day of that year (01/01/YYYY).

The DATE function takes in a year, month and day and gives you a date. To convert A1 into a full date, type the DATE function =DATE(year, month, day).


  • Plug A1 into the year slot, "1" into the month slot and "1" into the day slot. Now you have 1987-01-01.

"=DATE(A1, 1, 1)" yeilds "1987-01-01" when "1987" is in cell A1.
  • To expand this function, click on the little green button on the bottom right of the cell and drag down to apply the DATE function to every year in column A.

The Dates are extended 13 rows down.

What if I want to format dates into just a year, month or day?

  • Use the YEAR function on any date to extract just the year (as seen below). There is also a MONTH function and a DAY function.

"=YEAR(A1)" yeilds "2021" when the date "2021-07-01" is in A1

Format Dates In a Column

If you have some dates next to each other in the same column, and there is a recognizable pattern, you can expand the column to continue that pattern.

  • Choose your starting dates. In this case, the recognizable pattern is that they are seven days apart (this also works for one day apart, etc.).

The green button at the bottom left of the cell is emphasized.
  • Highlight your start dates.
  • Click on the little button on the small button on the bottom right of the highlighted area.
  • Drag down as many rows as you want the pattern to continue.

A column of dates is represented, where each dates is 7 days later than the last.

Conclusion

Of course, there are many more features available in Excel. However, the concepts presented here should get you well on your way if you need to format dates.

Want receive the best maketing insights? Subscribe now!

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Massa adipiscing in at orci semper. Urna, urna.

Thanks for joining our newsletter.
Oops! Something went wrong.