How to Use Short Date Number Format in Excel

Excel is a powerful tool for working with data, and one of the most common tasks is working with dates. In Excel, dates can be entered in a variety of formats, but the most common is the short date format. In this article, we will discuss four different methods to use short date number format in Excel.

We have a sample dataset, where different types of data such as, stock opening price, closing price, adjusted closing price, and volume of a company are listed against some random dates of December, 2022.

Before starting the article, let’s have an overview of the final output you are going to get.

Overview to Short Date Number Format 


Download Practice Workbook

You can download and practice the dataset we used to prepare this article.


How to Change Default Date Format in Excel

The first thing to understand is that Excel stores date as numbers, with the number 1 representing January 1st, 1900. An additional number represents each day, so January 2nd, 1900 is represented by the number 2, and so on. This means that when you enter a date into Excel, it is actually stored as a number.

Now, let’s get to know how to change the default date format if necessary. Let’s take a dataset named TESLA Stock Price in December 2022. Generally, the default date format in excel remains as mm/dd/yyyy. But, if we want to change that default format, we need to follow these steps:

  1. Go to the bottom right corner of your computer screen. And right-click upon the clock.

Selecting Default Short Date Number Format 

  1. From the drop-up bar, select the Adjust date/time A new window will pop-up.

Clicking on Adjust Date to Set New Default Date

  1. Then select the Date, time & regional formatting

Selecting a Place Where Different Formattings are Available

  1. Afterward, you can do two things. Either You can go to the Regional format option and change the regional format from the drop-down menu or you can select the change data formats option and change the format from there (limited options)

Showing Different Ways to Change the Default Date

  1. Now, change the format.

Different Regional Options to Change the Default Short Date

  1. Return to your Excel file. You will see that the previous short-date format inside your worksheet has a new default format.

Default Short Date Format Result


6 Methods to Manipulate Short Date Number Format in Excel

We are taking the aforementioned dataset to demonstrate the 6 methods below. You can use any dataset suitable for you.

Dataset


1. Return to Short Date Format Using Home Tab

This is the simplest method of all. The Home Tab is located inside the default ribbon.

Steps:

  • Firstly, select the cells from B7 to B27 & click on Home>Number (Drop down)

Short Date Format Using Home Tab Input

  • Then select Short Date & the date format is changed.

Short Date Format Using Home Tab Result


2. Return to a Different Short Date Format Using Format Cells

In this method, we are going to use the Format Cells option to convert the dates into our desired short date format.

Steps:

  • Firstly, select the cells from B7 to B27, right-click on them & select Format Cells…

Short Date Format Choosing Format Cells 

  • A new prompt will be opened. From there, select Date>03/14/12 (any Type)>OK. And the format will be changed successfully.

Short Date Format Using Format Cells Result


3. Short Date Format Using Format Cells (Custom)

This method is almost the same as the previous one. Here, some unconventional methods are available for use.

Steps:

  • Firstly, select the cells from B7 to B27, right-click on them & select Format Cells… (Same as the first step of the previous method)

Short Date Format Choosing Format Cells

  • A new prompt will be opened. From there, select Custom>($-en-GB)dd/mmmm/yyyy(any Type)>OK. And a new format will appear.

Short Date Format Using Format Cells Result


4. Using TEXT Function to Define a Short Date

In the case of this method, we are going to use an Excel Function named TEXT in a different column beside the primary date column to format the date according to our needs.

Steps:

  • To begin, go to cell C7 and type the following formula:
=TEXT(B7:B27,”mm/d/yyyy”)

Short Date Format Using TEXT Function Formula

  • After that, press ENTER. And all the cells ranging C7:C27 will consist of the same date as B7:B27 but in a different format.

Short Date Format Using TEXT Function Result


5. Using a VBA Code to Change Short Date Format

At this point, we are going to generate a simple VBA code to manipulate the present date format according to our demands.

Steps:

  • To start this method, click on Visual Basic under the Developer tab. (If you have not used this tab before, it may not be inside the title bar at first. You have to add it manually. Here is the procedure for how to add the developer tab in the title bar).

How to Open Visual Basic Window

  • Next, go to the Insert tab, and from the drop-down menu select Module. A new module will be available for writing the code.

Opening VBA Module

  • Afterward, start writing the code. When the coding is complete, click on the play button to run the code.

VBA Code to Change Short Date Format

Code

Sub VBA_FormatDate()

Range("B7:B27").NumberFormat = "mm/d/yyyy"

End Sub

Code Break Down:

After declaring the subheading, the range of cells from B7 to B27 is set for a definite number format. Which is: “mm/d/yyyy”. It means, the first two digits will be months, second one or two (if necessary) digit(s) will be date. And the last four digits will be year.

  • Finally, return to the excel worksheet window and observe that the date format is changed accordingly.

Short Date Format Using Short VBA Result


6. Using Another VBA Code to Change Short Date Format

At the end of this article, we are going to generate another VBA code to carry out the same function as before.

Steps:

  • Same as before, click on Visual Basic under the Developer tab.

How to Open Visual Basic Window

  • Like before, go to the Insert tab, and from the drop-down menu select Module. A new module will be available for writing the code.
  • Opening VBA Code ModuleThen as before, start writing the code. When the coding is complete, click on the play button to run the code.

Another VBA Code to Change Short Date Format

Code

Sub VBA_FormatDate2()

Dim Dorime As Worksheet

Set Dorime = ThisWorkbook.Sheets(7)

Dorime.Range("B7:B27").NumberFormat = "mm/d/yyyy"

End Sub

Code Break Down:

  • After declaring the subheading, A variable named “Dorime” is defined.
  • Then we set the range of Dorime as B7:B27.
  • Lastly, the number format of this range is defined as “mm/d/yyyy”, meaning the first two digits will be months, second one or two (if necessary) digit(s) will be date. And the last four digits will be year.
  • Finally, return to the excel worksheet window and observe that the date format is changed accordingly.

Short Date Format Using Another VBA Result


Things to Remember

  • You may not have to use the methods from 4 to 6 if you do not want to use an unconventional customized date format.
  • If the dataset which contains the dates, does not include all the dates serially, be careful to use the Autofill function. Because it may ruin your data accuracy.

Conclusion

In conclusion, Excel offers a variety of short date number format for entering and displaying dates. The most common format is the mm/dd/yyyy format, but other formats are also available. Additionally, Excel allows you to create custom date formats to suit your specific needs. Understanding how to use these different date formats can help make working with dates in Excel more efficient and accurate. You can drop any comment in the comment section below if you have any queries. Do explore Exceldemy to get more articles like this.

Md. Nafis Soumik

Md. Nafis Soumik

I am Md. Nafis Soumik. I am a Naval Architecture & Marine Engineering (NAME) graduate from Bangladesh University of Engineering & Technology (BUET). My hobby is to listen and create music along with backpacking. My career goal is to pursue higher education eventually. I always attempt to learn from many sources and try to come up with creative answers.

We will be happy to hear your thoughts

Leave a reply

5 Excel Tips
You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo