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

 

The dataset showcases Store Number, Quantity of Sales, and Date.

leading zero in excel date format

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

leading zero in excel date format, TEXT function

  • Press ENTER to see the output: 01012022.
  • Drag down the Fill Handle to see the result in the rest of the cells.

leading zero in excel date format, TEXT function

This is the output.

leading zero in excel date format, TEXT function

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.

leading zero in excel date format, using Apostrophe

  • Press ENTER.
  • The output will display a leading zero.
  • Drag down the Fill Handle to see the result in the rest of the cells.

leading zero in excel date format, using Apostrophe

If you click a date with a leading zero header (E5), an error will displayed in D5.

leading zero in excel date format, using Apostrophe

  • Click on the error icon > select Ignore Error.

leading zero in excel date format, using Apostrophe

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:E15Home > General > Number.

leading zero in excel date format, using Custom Number format

  • In the Format Cells window, go to Number.
  • Choose Custom and enter 00000000 in Type.
  • Click OK.

leading zero in excel date format, using Custom Number format

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


The leading zero is added to all cells inE5:E15.

leading zero in excel date format, using Custom Number format

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.
=CONCATENATE("0",D5)

leading zero in excel date format, using CONCATENATE function

  • Press ENTER to see the output: 01012022.
  • Drag down the Fill Handle to see the result in the rest of the cells.

leading zero in excel date format, using CONCATENATE function


Method 5 – Applying the Ampersand Operator

  • Enter the following formula in E5.
="0"&D5

The formula adds 0 before the value 101202201012022.

leading zero in excel date format, using Ampersand operator

  • Press ENTER and drag down the Fill Handle.

leading zero in excel date format, using Ampersand operator

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.

=RIGHT("00000000"&D5,8)

leading zero in excel date format, using RIGHT function

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

using RIGHT function


Method 7 – Applying a Combination of the RIGHT and CONCATENATE Functions

  • Use the following formula in E5.
=RIGHT(CONCATENATE("00000000",D5),8)

combination of RIGHT and CONCATENATE functions

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

combination of RIGHT and CONCATENATE functions


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)

using BASE function

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

using BASE function


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

using REPT and LEN functions

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

using REPT and LEN functions


Download Practice Workbook

 


Related Articles


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

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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo