Shajratul Alam Towhid
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.
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.
- 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.
Consequently, we’ll get all the outputs with a leading zero like this.
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
- 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 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.
- 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.
Consequently, leading zero is added to all the cells of E5:E15.
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)
- Secondly, press ENTER to get the output as 01012022.
- Thirdly, use Fill Handle to get all the outputs with leading zero.
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"&D5Here, this formula puts 0 first then the value 1012022 in the D5 cell which finally makes the output 01012022.
- Secondly, press ENTER and use Fill Handle to get all the outputs of cells E5:E15.
Similar Readings
- How to Put 0 in Excel in Front of Numbers (5 Handy Methods)
- How to Add Leading Zeros to Make 10 Digits in Excel (10 Ways)
- [Solved]: Leading Zero Not Showing in Excel (9 Possible Solutions)
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 Fill Handle to have the desired output with 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.
