Inside The Web
    Facebook Twitter Instagram
    Inside The Web
    • Office
      • Excel
      • Ms Word
      • Gmail
    • Productivity
      • Time Management
      • Task Automation
    Inside The Web
    Home»Excel»5 Ways to Format a Date in Excel

    5 Ways to Format a Date in Excel

    AsmiBy AsmiFebruary 5, 2023 Excel 4 Mins Read

    You can write dates in many ways. Excel, by default, formats the date in your system time date format. This might not always be feasible for you or may not align to your database’s theme.

    For instance, you could be creating a spreadsheet for an organization that follows a standardized format or a different format according to its location.

    Regardless of the reason, Excel has made it pretty convenient for users to use different date formats. You could choose date formats depending on the region or even create a custom date format. Stick with us as we go into detail about how you can format dates in Excel.

    Change Format to Long or Short Dates

    Convert long and short dates Excel

    If you’re okay with your system format but just want the dates to remain longer or shorter, you can make this change from the Home tab of your Excel program. For example, if you’ve entered your short date as 02-02-23, changing the date to a longer format will look something like Thursday, February 2, 2023.

    1. Select your cell or cell range from the grid.
    2. Head to the Home tab to locate the Number section.
    3. Select the drop-down menu and choose either Short Date or Long Date.
      Short Date Long Date

    Choose a Library Date Format

    Excel offers plenty of library formats you could choose from. If you’re looking to convert your dates into one of the popular formats, you can look for the format in the library options.

    1. Select the fly-out menu in the Number section of the Home ribbon.
    2. Choose More Number Formats.
      More Number Formats Excel
    3. Under Category, select Date.
    4. Specify the locale under Locale (location).
      Change Locale Format Cells Excel
    5. Select a date format under Type.
      Format Date cell type Excel
    6. Click OK.

    Create Custom Date Format

    If you have a specific format in mind, you might just have to create your own date format. As challenging as this might sound, the steps are actually pretty simple. All you have to do is take note of what the character code means while entering your own custom format.

    Here is what different characters mean while creating your date format

    CharacterMeaningExample (2nd February 2023)
    dSingle Digit Day2
    ddDouble Digit Day02
    mSingle Digit Month2
    mmDouble Digit Month02
    mmmAbbreviated Month NameFeb
    mmmmLong Month NameFebruary
    mmmmmInitial Letter of a MonthF
    yyLast Two Digits of a Year23
    yyyyFull Year2023

    Once you’re familiar with these characters, here’s how you can create your custom date format:

    1. Select the cell/cell range you wish to apply the formatting.
    2. On your keyboard, use Ctrl + 1.
    3. Under Category, select Custom.
    4. Enter your character under Type. You can use separators like ‘/’, ‘-’ to separate your dates.
      Custom cell Type Excel
    5. Check the preview of your format under Sample.
      Custom Format Sample Excel
    6. Click OK.

    Use TEXT() Function

    You can also use the TEXT() function to convert your date format. This method is only useful if you’re looking to convert pre-existing data from your grid.

    In an empty cell, enter the TEXT function in the following format:

    =TEXT(cell, format)

    Text function Excel

    In this example, we’re converting 02-02-23 into February 02, 2023. To obtain the following result, we used this formula:

    =TEXT(A1, “mmmm dd, yyyy”)

    Flash Fill Excel

    To convert the remaining range, select the cell you entered your formula in. Place your cursor on the bottom-right corner of the cell, then drag and release it. This method is called Flash Fill.

    Text to Columns

    Text to Columns is a great tool to use when you’re looking to convert a bigger data set at the same time. The utility is, however, limited to only a few generic formats so if you’re looking for something unique, this might not be the best method for you.

    1. Select your data range.
    2. Head to the Data tab and select Text to Columns.
      Text to Column Excel
    3. In Step 1 of 3, choose Delimited.
      Text to Columns Step 1 of 3
    4. In Step 2 of 2, deselect all boxes.
      Text to Columns Step 2 of 3
    5. In Step 3 of 3, select Date, then click on the fly-out menu to pick a format.
      Text to Columns Step 3 of 3
    6. Click Finish.

    Text to Columns may not work if Excel fails to understand the way your date is formatted in. For example, it may confuse days for months, and vice-versa. So, we only recommend you use this method if you’re looking to change the formatting of dates with days exceeding 12.

    Change Default Date Format

    If you normally use the date format you’re looking to convert your dates into, it would be better if you changed the default date format of your system. This will save you from the hassle of changing formats every time you make an entry in Excel.

    1. Open the Run command (Windows key + R).
    2. In the search box, enter intl.cpl then hit Enter.
      Run command Windows
    3. Select a Format.
      Change Region Format Locale
    4. Under Date and time formats, locate short and long dates.
      Change Region Format Windows
    5. Click on the fly-out menu and choose a date format for these options.
    6. Click OK to save changes.
    Excel Basics
    Asmi

      Asmi is a content writer specializing in MS Office. She loves writing Excel-related content to assist other users to learn their way around the program. She started using Excel when she first entered the industry as a marketing intern. Soon, she realized that learning the often-overlooked spreadsheet program was no piece of cake. In her articles, she attempts to brief out complex topics to help beginner to intermediate-level Excel users. Asmi uses Excel for everything, including creating a grocery list to managing her personal finance. She’s a bit dramatic when it comes to the Flash Fill feature; so much so that could write a eulogy out of it. When she’s not working, you’ll find her learning a new recipe, or cleaning up her mess (out of the failed recipe).

      Add A Comment
      Table of Contents
      • Change Format to Long or Short Dates
      • Choose a Library Date Format
      • Create Custom Date Format
      • Use TEXT() Function
      • Text to Columns
      • Change Default Date Format
      Recent Posts
      • How to Calculate Discount Percentage in Excel
      • How to Create a Progress Bar in Excel
      • What is VSTACK in Excel
      • How to Separate Dates in Excel
      • How to Lock a Cell in Excel Formula
      • Home
      • Privacy Policy
      © 2023 Inside The Web

      Type above and press Enter to search. Press Esc to cancel.