How to Change Date Format in Excel

puneet-gogia-excel-champs

- Written by Puneet

One of the best things about Excel is that you can change date formats and apply a format according to your need or the way you want to show it to the user.

In this tutorial, we’ll learn how to change the format of the date as it is very important to analyze the sales, profits, and events.

Steps to Change Date Format in Excel

Below are the steps to quickly change the format of the date in Excel.

  1. First, select the cell or range of cells where you would like to change the date format.
  2. Next, go to the “Home” tab and then click on the “Number” group.
  3. After that, click on the Custom drop-down box from the number group, and appears the “Long Date” and “Short Date” options.
    select-short-long-date-date-format
  4. The Long Date format is displayed as (02 January 2000) and the Short Date looks like (02-01-22). You can either choose one of these.

There are “More Number Formats” option which is at the bottom of it. Click here to get more date formats.

click-on-more-number-formats

Once you click, the format cell window opens, and there you need to choose the Date category.

dialog-box-with-date-formats

Here are the date format types, locations, and language shown along with its sample. You can select the particular format and it shows the sample that gives you a hint of how it looks on the worksheet.

In the end, click the “OK” button after finalizing the date format you want to use.

select-final-format-date-format

Create Your own Custom Date Format

No doubt, there are several formats available by default. But in case, you want to make the format as per your need then you can make it by using some specific codes.

    1. To do this, you have to first select the range of cells that contain the dates.
    2. Then, press (Ctrl+1) to open the format cell dialog box and click on the “Custom” option which is at the bottom of the category list.
      use-custom-option-date-format
    3. Now, you’ll notice there is a specific code in the Type text box, and you can re-create or edit this code to make your own format.
    4. Once you’ve done this, click “Ok” and the selected cells will change into the new format.

check-sample-date-format

The moment you click OK it changes the date format to the custom format that I have specified.

final-screen-with-date-format

You can use the following table to create your custom date.

Code
Describes:
Example
dd
Day number
01
mm
Month number
02
yyyy
Full-year
2022
mmm
Month name(short)
Jan
yy
Year last two digits
22
Last Updated: October 01, 2023

Leave a Comment