The dataset showcases Store Number, Quantity of Sales, and Date.
The date format mm dd yyyy without dash or slash is used.
Method 1 – Applying the TEXT Function to Keep the Leading Zero in a Date Format
- Enter the following formula in E5.
=TEXT(D5,"00000000")
D5 is the Date 1012022 in which we want to add the leading zero. 00000000 has 8 digits, which means the date with the leading zero will have 8 digits. If the original date has 7 digits, 1 leading zero will be added. If the original date has 6 digits, 2 leading zeros will be added.
- Press ENTER to see the output: 01012022.
- Drag down the Fill Handle to see the result in the rest of the cells.
This is the output.
Read More: How to Keep Leading Zeros in Excel
Method 2 – Using the Apostrophe to Keep the Leading Zero in a Date Format
- Copy the date in D5 and paste it into E5.
- Enter an Apostrophe before the date in E5.
- Press ENTER.
- The output will display a leading zero.
- Drag down the Fill Handle to see the result in the rest of the cells.
If you click a date with a leading zero header (E5), an error will displayed in D5.
- Click on the error icon > select Ignore Error.
- Apply the same procedure to all cells in Column E.
Read More: How to Add Trailing Zeros in Excel
Method 3 – Utilizing a Custom Number Format to Keep the Leading Zero in a Date Format
- Copy Column D and paste it in Column E . Add a leading zero to the cells in Column E.
- Select the E5:E15 > Home > General > Number.
- In the Format Cells window, go to Number.
- Choose Custom and enter 00000000 in Type.
- Click OK.
Note: You can also press CTRL + 1 to open the Format Cells dialog box.
The leading zero is added to all cells in E5:E15.
Read More: How to Keep Leading Zeros in Excel CSV
Method 4 – Using the CONCATENATE Function
- Enter the following formula in E5.
=CONCATENATE("0",D5)
- Press ENTER to see the output: 01012022.
- Drag down the Fill Handle to see the result in the rest of the cells.
Method 5 – Applying the Ampersand Operator
- Enter the following formula in E5.
="0"&D5
The formula adds 0 before the value 1012022: 01012022.
- Press ENTER and drag down the Fill Handle.
Read More: [Solved]: Leading Zero Not Showing in Excel
Method 6 – Utilizing the RIGHT Function
- Use the following formula in E5.
=RIGHT("00000000"&D5,8)
- Press ENTER and drag down the Fill Handle.
This is the output.
Method 7 – Applying a Combination of the RIGHT and CONCATENATE Functions
- Use the following formula in E5.
=RIGHT(CONCATENATE("00000000",D5),8)
- Press ENTER and drag down the Fill Handle.
This is the output.
Method 8 – Using the BASE Function
- Use the following formula in E5.
=BASE(D5,10,8)
- Press ENTER and drag down the Fill Handle.
This is the output.
Method 9 – Utilizing the REPT and the LEN Functions
- Enter the formula in E5.
=REPT(0,8-LEN(D5))&D5&""
- Press ENTER and drag down the Fill Handle.
This is the output.
Download Practice Workbook
Related Articles
<< Go Back to Pad Zeros in Excel | Number Format | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!