# 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 understandable. In this article, weâ€™ll try to discuss how to add a leading zero in Excel date format.

## How to Keep Leading Zero in Excel Date Format: 9 Ways

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.

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

• 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.

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

Consequently, weâ€™ll get all the outputs with a leading zero like this.

### 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

• 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.

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.

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

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

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

Using the Custom Number format in adding a 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 Column D to Column E . Weâ€™ll add a 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.

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

Note: You can just press CTRL + 1 to open the Format Cells dialog box.

### 4. Using CONCATENATE Function

One of the most common functions that 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)`

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

### 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.

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

### 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)`

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

### 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)`

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

### 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)`

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

### 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&""`

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

## 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

<< Go Back to Pad Zeros in Excel | Number FormatÂ |Â Learn Excel

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shajratul Alam Towhid

Md Shajratul Alam Towhid, a BSc graduate in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, holds a pivotal role as an Excel & VBA Content Developer at ExcelDemy. Fueled by a deep passion for research and innovation, he actively engages with Excel. In his capacity, Towhid not only adeptly tackles complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his unwavering commitment to consistently delivering exceptional, high-quality content that... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF