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.

**Table of Contents**hide

## 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”&D5`

Here, 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.