How to Manage Dates in Excel

How to Manage Dates in Excel

Knowing how to manage dates in Excel is a valuable skill. This guide shows you how to manage different formats of dates in your Excel sheet.

I will use the Office 365 version of Excel (build 2203) for this guide. However, the instructions will be the same for other versions.

Managing Date Formats in Excel

Around the world, different formats are used to write dates. Excel can usually recognize when someone enters a date in a frequently used format.

However, if Excel is unable to recognize the entered information as a date or if you wish to change to a custom format, you can follow these steps:

  1. Enter the date in your desired format.

    Enter the date.
    Enter the date in your desired format.

  2. Check the Number section in the Home tab. If Excel recognizes the entered information as a date, it will be formatted as a date in the box.
    Check the number section
    Check the number section from the Data tab.

    In this case, Excel could not recognize the information correctly, so it is formatted as General.

    To use different functions available with the date format, you will need to change the cell formatting from General to Date.

  3. To change the cell formatting to Date, open the Number Format drop-down menu.
    Open the format drop-down menu
    Open the Number Format drop-down menu.

    This menu contains two date formats. The Short date format shows dates in numbered form. In a Long date format, the Month is written in text form.

  4. To format your cell to a short date, select the Short Date option from the menu.

    Format as short date
    Format the cell as a Short Date.

  5. If the entered date contains text, you should use the Long Date format to display it in text form.
    Long date format.
    You can select the Long Date format from the drop-down menu.

    After formatting the cell as a Long Date, it is displayed as follows:

    Long date display
    This is the long date format in Excel.

Using Custom Formatting for Dates

Excel also has the option to use custom date formatting. This allows you to select the exact date format you’re looking for.

Here’s how you can apply custom formatting for dates in Excel.

  1. Select the cell that contains the date. In the Home tab, navigate to the Cells section and open the Format drop-down menu.
    Open format cells menu
    Navigate to the cells section and open the Format drop-down menu.

    This menu contains multiple formatting options for your Excel sheet. To format the data type entered in your selected cell, open the Format cells menu.

    Open the Format cells menu
    Open the Format cells menu.

  2. In the Category section, select Date.

    Select the date category from the category section.
    Select the Date category.

  3. The Type section contains multiple date formats that Excel recognizes. You can also open the Locale drop-down menu to view date formats used in your selected location.

    Type section contains multiple date formats
    The Type section contains multiple date formats.

  4. Select your desired format and click OK to apply the date formatting.
    Select the date format and click OK
    Select the Date format and click OK.

    Your custom formatting is complete.

    Custom formatting is complete
    Custom formatting is complete.

Phoebe is an HR assistant in her day job, but is also a web blogger that loves writing guides and sharing her experiences. When she is not out with her friends, she enjoys traveling or binge-watching Netflix. She graduated with a BA in Communication and Media Studies from the University of Utah, and is a chief editor at TechObservatory.

Leave a Comment