# How to Keep the Leading Zero in an Excel Date Format – 9 Methods

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.

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

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

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

### Method 6 – Utilizing the RIGHT Function

The RIGHT function takes a text and a number as input and returns the text with the specified number of characters from the right as output.

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

The BASE function takes the base value of the number system as input and returns it in text format with the specified number of digits.

• 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

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

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
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