How to Keep Leading Zero in Excel Date Format (9 Quick Ways)

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for how to keep leading zero in Excel date format, then you are in the right place. In real-life examples, we often need to use Excel where we must put dates. Adding leading zero in the date format makes it more presentable and understanding. In this article, we’ll try to discuss how to add leading zero in Excel date format.


Download Practice Workbook


9 Ways to Keep Leading Zero in Excel Date Format

Excel offers plenty of methods to add leading zero in the date format. To show this, we made a dataset named Sales in Different Stores having column headers as Store Number, Quantity of Sales, and Date. The dataset is like this.

leading zero in excel date format

Here, we have used the date format as mm dd yyyy without giving any slush among them.
We’ll now discuss the methods to add leading zero in Excel date format.


1. Applying TEXT Function to Keep Leading Zero in Excel Date Format

We can use the TEXT function to add leading zero.

  • To use this function, firstly write the following formula in the E5 cell of the dataset below.
=TEXT(D5,"00000000")

Here, D5 is the Date 1012022 in which we want to add leading zero. 00000000 has 8 digits which means the date with leading zero will have 8 digits. If the original date has 7 digits, then 1 leading zero will be added. If the original date has 6 digits, then 2 leading zeros will be added.

leading zero in excel date format, TEXT function

  • Secondly, press ENTER to get the output as 01012022.
  • Thirdly, use Fill Handle by dragging down the cursor by holding the right-bottom corner of the reference E5 cell.

leading zero in excel date format, TEXT function

Consequently, we’ll get all the outputs with a leading zero like this.

leading zero in excel date format, TEXT function

Read More: Add or Keep Leading Zeros in Excel (10 Suitable Ways)


2. Using Apostrophe to Keep Leading Zero in Excel Date Format

We don’t need any function if we use the Apostrophe before the original Date. Using Apostrophe (‘) adds leading zero automatically.

  • To show this, firstly, copy the date in the D5 cell and paste it into the E5
  • Secondly, just put an Apostrophe before the date in the E5

leading zero in excel date format, using Apostrophe

  • Thirdly, press ENTER.
  • Eventually, we’ll get the output with a leading zero.
  • Fourthly, use Fill Handle to get all the dates with a leading zero.

leading zero in excel date format, using Apostrophe

Additionally, if we click a cell in the Date with Leading Zero header, suppose an E5 cell, an error icon will occur in the adjacent D5 cell like the picture below.

leading zero in excel date format, using Apostrophe

  • Fifthly, to avoid this, click on the icon > select Ignore Error.

leading zero in excel date format, using Apostrophe

Eventually, the icon will disappear. We need to do the same thing for every cell in the E Column.

Read More: How to Add Leading Zeros in Excel Text Format (10 Ways)


3. Utilizing Custom Number Format to Keep Leading Zero in Excel Date Format

Using the Custom Number Format in adding leading zero is a quite common method. We just need to know how many numbers we want to put in the date format.

  • Initially, copy and paste the database of the D Column to E Column. We’ll add leading zero in the cells of the E Column.
  • Secondly, select the cells E5:E15 > go to Home > choose General > select the icon of Number.

leading zero in excel date format, using Custom Number format

  • Eventually, a Format Cells window will appear.
  • Thirdly, go to Number > choose Custom > write 00000000 in the Type
  • Fourthly, click OK.

leading zero in excel date format, using Custom Number format

Note: You can just press CTRL + 1 to open the Format Cells dialog box.
Consequently, leading zero is added to all the cells of E5:E15.

leading zero in excel date format, using Custom Number format

Read More: How to Keep Leading Zeros in Excel CSV (4 Easy Ways)


4. Using CONCATENATE Function

One of the most common functions which we can use to add leading zero is the use of the CONCATENATE function. This function has amazingly simple strings where we only need to place a 0 and give the reference cell.

  • We can write the following formula in the E5 cell of the dataset below.
=CONCATENATE("0",D5)

leading zero in excel date format, using CONCATENATE function

  • Secondly, press ENTER to get the output as 01012022.
  • Thirdly, use Fill Handle to get all the outputs with leading zero.

leading zero in excel date format, using CONCATENATE function

Read More: How to Add Leading Zeros in Excel by CONCATENATE Operation


5. Applying Ampersand Operator

We can use Ampersand instead of directly using the CONCATENATE function. It gives the same result that the CONCATENATE function returns.

  • To use this, firstly, write the following formula in the E5 cell of the dataset below.
="0"&D5

Here, this formula puts 0 first then the value 1012022 in the D5 cell which finally makes the output 01012022.

leading zero in excel date format, using Ampersand operator

  • Secondly, press ENTER and use Fill Handle to get all the outputs of cells E5:E15.

leading zero in excel date format, using Ampersand operator


Similar Readings


6. Utilizing RIGHT Function

The RIGHT function has syntax to give input of the total digits of the date format. It takes a text and a number as input. And returns the text with the specified number of characters from the right as output.
We can write the following formula in the E5 cell of the dataset below.

=RIGHT("00000000"&D5,8)

leading zero in excel date format, using RIGHT function

  • Secondly, press ENTER and use the Fill Handle.
  • Finally, we’ll get the output like this.

using RIGHT function


7. Applying Combination of RIGHT and CONCATENATE Functions

We can use the combination of RIGHT and CONCATENATE functions to add leading zero.

  • Firstly, we can write the following formula in the E5 cell like this.
=RIGHT(CONCATENATE("00000000",D5),8)

combination of RIGHT and CONCATENATE functions

  • Similarly, press ENTER and use Fill Handle to have the desired output with leading zero like this.

combination of RIGHT and CONCATENATE functions


8. Using BASE Function

The usage of the BASE function is another easy method to add leading zero. It takes a value and the base value of the number system as input and returns it in text format with the specified number of digits.

Eventually, we can write the formula of the BASE function in the E5 cell like this.

=BASE(D5,10,8)

using BASE function

  • Secondly, press ENTER.
  • Thirdly, use the Fill Handle.
  • Consequently, we’ll get the output like this.

using BASE function


9. Utilizing REPT and LEN Functions

We can use the combination of REPT and LEN functions to keep leading zero effectively.

  • Firstly, write the following combined formula in the E5
=REPT(0,8-LEN(D5))&D5&""

using REPT and LEN functions

  • Similarly, press ENTER and use Fill Handle for the desired output like this.

using REPT and LEN functions


Conclusion

This way, we can keep any number of leading zeros in the Excel date format if we study this article properly. Please feel free to visit our official Excel learning platform ExcelDemy for further queries.


Related Articles

Shajratul Alam Towhid

Shajratul Alam Towhid

Hello, myself Shajratul Alam Towhid. Basically, I am a Naval Architect who wants to expand knowledge in the field of Microsoft Excel. I wish all of my articles will be beneficial for the readers.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo