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

Use the TEXT function to add leading zero.

• Enter the following formula in E5.

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

Using the apostrophe (‘) automatically adds a leading zero .

• 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 inE5:E15.

Read More: How to Keep Leading Zeros in Excel CSV

Method 4 – Using the CONCATENATE Function

Use the CONCATENATE function.

• Enter the following formula in E5.

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

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

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.

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

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

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

• Press ENTER and drag down the Fill Handle.
• This is the output.

Download Practice Workbook

Related Articles

• How to Remove Leading Zeros in Excel
• How to Remove Zeros in Front of a Number in Excel

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