How to Change Date Format in Excel


Home >> How to Use Excel Tutorial >> Excel Tips and Trick >> How to Change Date Format in Excel



In this tutorial, you will learn how to convert date format in excel. The ways of how to change date format in excel can be done with two approaches: utilizing Format Cell feature or using TEXT formula.


Why do We Need to Learn How to Change Date Format in Excel?


In date data processing on a spreadsheet, sometimes we need to adapt the form of our data so it will fit with our needs. This is needed to beautify its look or fit the look with the form that is most often seen by the people around us. This adaptation is nice to do but it will take a lot of time if there are much data to be changed in our spreadsheet. Besides that, if we do manual writing to change the form of the date, then there is a possibility that the data type changes into text and thus they cannot be calculated again.

As an example of this need in our job, imagine we process check-in and check-out data in hotel business. Maybe we need the check-in and check-out time data in various places like the notification template for the occupants or as a report material for internal use. The date writing that is needed in each of the places is different and for that, we need to adapt it to each of our needs when using the data. But, of course, we want a much faster way than just changing them manually so they can be fit into our needs.

To help the conversion process, you can use spreadsheet built-in features to help you do it. The features are available in the form of cell format and TEXT formula. The two of them can be used to automate and make the date form change process faster. They will be useful to be learned and understood if you sometimes need to process data of this kind.


What is How to Change Date Format in Excel?


How to change date format in excel is an available feature in the spreadsheet to change the form of this kind of data writing to fit it into our needs. As there are many kinds of writings for a date data, then this feature can help you to adjust it to your need.

Generally, if you use TEXT formula to do the change, then the input can be explained as follows:


=TEXT(value, format_text)


Note:
  • value = the data to be changed its form of writing
  • format_text = the form that you need



How to Set Date Format in Excel?


The following will explain how to change date format in excel using two approaches: cell format and TEXT.


The Methods of Stopping Ms. Excel Program

  1. Highlight the cell where the number to be converted is in

    How to Change Date Format in Excel - Screenshot of Step 1-1

  2. Right-click on the cell and click Format Cells… or click Number Format Dropbox in the Home tab and choose More Number Formats

    How to Change Date Format in Excel - Screenshot of Step 1-2-1

    How to Change Date Format in Excel - Screenshot of Step 1-2-2

  3. Highlight Date or Custom

    How to Change Date Format in Excel - Screenshot of Step 1-3

  4. Choose the date form that you desire

    How to Change Date Format in Excel - Screenshot of Step 1-4

  5. Click Ok

    How to Change Date Format in Excel - Screenshot of Step 1-5

  6. The process is done!

    How to Change Date Format in Excel - Screenshot of Step 1-6




Approach 2: TEXT

  1. Type equal sign ( = ) in the cell where you want to put the result in

    How to Change Date Format in Excel - Screenshot of Step 2-1

  2. Type TEXT (can be with large and small letters) and open bracket sign after =

    How to Change Date Format in Excel - Screenshot of Step 2-2

  3. Input data or cell coordinate where the data that you want to process is in, after open bracket sign, then input a comma sign ( , )

    How to Change Date Format in Excel - Screenshot of Step 2-3

  4. Type your desired digit form between quotes sign ( “” )

    How to Change Date Format in Excel - Screenshot of Step 2-4

  5. Type close bracket sign

    How to Change Date Format in Excel - Screenshot of Step 2-5

  6. Press Enter
  7. The process is done!

    How to Change Date Format in Excel - Screenshot of Step 2-7



Exercise


After you have learned the two approaches on how to change date format in excel, now is the time you do the exercise on how to change date format in excel to deepen your understanding!

Download the exercise file and do all the instructions. Download the answer key file if you have done the exercise and want to check your answers or if you are confused about how to do the exercise!

Link of the exercise file:
Download here

Instructions:
Change the data form in the gray cells in the appropriate row number!
  1. Change it into this: 8-Aug-08
  2. Change it into this: 08/08/08
  3. Change it into this: 08 Aug 2008 (Hint: Use “Custom” then type in the data form that you need!)

Link of the answer key file:
Download here


Additional Note


TEXT is also useful to format other numeric data than dates. If you want to understand it further, then please read its tutorial in Compute Expert!



Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!


Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!



Follow our tutorial content also on


Hand-Picked CE Tutorials

Excel Calculation

How to Sum in Excel

Subtraction in Excel

Multiplication in Excel

Division Excel Calculation

Average Excel Calculation



Excel Formula

VLOOKUP Excel Formula

Excel IF Function

SUM Formula in Excel

COUNTIF Formula in Excel

COUNT Function in Excel



Excel Tips and Trick

How to Print in Excel

Convert Number to Text Excel

Excel Worksheet Definition

Excel Range Definition

How to Add Columns in Excel



Excel Consultation

Contact Us

Privacy Policy

Affiliate Disclosure

Terms & Condition



© 2021 Compute Expert