Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

How Do I Add A Leading Zero To A Date?

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I have imported a DBF file into Excel and have a column of dates that are missing the leading zero on single didgit months. When I try to us the custom format of mm/dd/yyyy it doesn't work (interestingly, after I select that format if I click on an individual cell it changes to the right format).

Does anyone know a better way to do this?

Thanks in advance!

View Answers     

Similar Excel Tutorials

Keep Leading Zeros in Numbers in Excel - 2 Ways
I'll show you 2 ways to add and keep leading zeros in front of numbers in Excel. These two methods are very simple ...
Join a Date and Time into a Single Cell using VBA Macros in Excel
In order to combine a cell that has a date with a cell that has a time, using a Macro and VBA in Excel, you must su ...
Quickly Enter the System Date in Excel - Keyboard Shortcut
You can insert the system Date by holding the Control (Ctrl) key and pressing the colon or semicolon key. (Ctrl+;) ...
Years, Months, Days Between two dates in Excel
I have found a rather convoluted method to Calculate the Number of Years, Months and Days between to Dates in Excel ...

Helpful Excel Macros

Format Cells in The Short Date Number Format in Excel
- This free Excel macro applies the Short Date number format to a selection of cells in Excel. This number format display
Format Cells in The Long Date Number Format in Excel
- This free Excel macro formats a selection of cells in the Long Date number format in Excel. The Long Date number format
Format Cells as Time in Excel
- This free Excel macro formats a selection of cells in the Time format in Excel. This Time number format means that inst
Format Cells as a Fraction in Excel Number Formatting
- This free Excel macro will automatically format a selected cell or many selected cells in the Fraction number format in
Format Cells in The Number (Numerical) Number Format in Excel
- This free Excel macro formats selected cells in the Number or Numerical number format in Excel. This means that the cel

Similar Topics


I export a report from SAP to Excel which generates leading zeros in the format mm/dd/yyyy, which Excel doesn't seem to recognize as dates. I changed the Format from whatever the query generated to Date format, so that the filter lists the dates chronologically. But the Sort function still doesn't.

Ex: 01/01/2007 shows up before 10/01/2006 when sorted.

How can I fix this? I can go into each cell and hit the Enter key, which causes 01/01/2007 to turn into 1/1/2007. But I can't do that for 2000 lines


Hi There.

I have imported some data from my software which includes dates also the date format what I received in excel is 1012009 although this 01-01-2009 (dd-mm-yyyy) but I failed to convert it to the date format it gives me 13-10-4670 if I applied custom format "dd-mm-yyyy" because it is considering it serial number of date, Text to column doesn't work, date, year, month, day, date value functions & custom format don't work.

any suggestion??????????????


I have an ID that is used in my spreadsheet with the following custom format:

End result:

I want the user to enter the numbers and then this format will enter the dashes. That works fine, but something else doesn't.
When the ID starts with a single digit, I want the format to enter the leading zero.
Yes - 01-12345-001
No - 1-12345-001

How can this be done?

Thank you in advance!

Hi everyone. I'm using Excel 2007, and I need to know how to format a cell so it keeps it's leading 0, and is actually a number not text. If I custom format it as "00000" then type in 06591. The leading 0 shows in the cell, but when you click on it, it's not really there. Same thing with formatting it as zip code. Any ideas?



1st question:
I have a birthdate column, which is in this format: 06/15/1983. I want to change it to this format, yyyymmdd. So I created a custom format and the birthdate now displays 19830615, which is great. However when I click on the cell the value in the function box states, 06/15/1983. I need it to display the 19830615 format, how do I go about this??

2nd question:
I have a SSN field which is displayed like this: 023-45-6789. I need to remove both of the -, so I tried a find and replace. It worked but if the SSN had a leading zero it took the zero off. I changed the column to a text format and it still took off the leading zero. How can I remove the -'s and keep the leading zeros??


i had a worksheet which i got from a client.In that worksheet i had the dates column in the (dd/mm/yyyy) format.But i want all the dates to be displayed in dd-mm-yyyy format.

For that i had formatted the Column by Selecting Cells>Format Cells>Custom>selected dd-mm-yyyy format.

Everything works fine in the raw display but whenever i click a Cell in the date the formula bar displays the same date as dd/mm/yyyy format.

I m using Excel 2007.

How would i make the cell appear everywhere in the DD-mm-YYYY format.
Please help me

Thanks in advance.

I have a spread sheet for a youth sport organization(National). In the DOB column they want it to be formatted yymmdd, the problem is all my DOBs are 2003, 2004, 2005. So for example if the date was 9/04/2003 it would then be formatted as 030904. It won't allow me to add the leading zero to get the format right.They want this to sort the dates by oldest to youngest, don't ask me why they decided on this format. I tried doing a custom format and still couldn't get it to come out right. Any advice?



I've read all the postings in the message board regarding how to get around the "leading zeros" problem and none of the messages help solve my problem.

I've got some data which I can not modify in its initial software application. When I exported it into excel, all of the leading zeros in one of my columns disappeared.

Some of the cells in this particular column contain letters only, some should contain 3-digit numbers with 1 leading zero, some should contain 4 digit numbers with 2 leading zeros, some should contain 5 digit numbers with 1 or 2 leading zeros and some should contain 6-digit numbers with no leadign zeros. All of the non-zero numbers in those cells properly displayed, but all of the leading zeros disappeared. There is a tremendous amount of data in this column and there is no way for me to know, just by looking at my data, which of the cells are missing 1 leading zero and which of the cells are missing 2 or 3 leading zeros because the data in that column does follow one specific format or one specific set of criteria.

Converting the cells to text didn't solve the problem. Formatting the cells using "custom" doesn't work either because "custom" only allows me to format text when all of the cells meet one specific criteria. For example, if I want all cells to contain six digits, using "custom" will correctly force excel to fill in three leadings zeros in all cells that contain only three numbers; however, my situation isn't that simple.

SOME of my cells require 2 leadings zeros (with 5 numbers in total in that cell); SOME require 3 leadings zeros (with six numbers in total in that cell) SOME require NO leadings zeros (with 4 numbers in total in that cell). Some of my cells are letters only. I need a formula or a macro or something that will allow Excel to read all of my cells in this particular column exactly as they were intended to be read. Converting the cells to text doesn't work and using "custom" doesn't work because custom only works when all of the cells meet the same criteria (i.e, all cells must contain XX number chateracters).

It's almost as if I need to tell excel to do the following: Insert 1 leading zero in all cases where the original data contained 1 leading zero, insert 2 leading zeros in all cases where the original data contained 2 leading zeros, etc., etc.

Does anyone know of a formula or way to include 1, 2 or 3 leading zeros ONLY in those cells in which the original data contained 1, 2 or 3 leading zeros?

I'm not very advanced with this stuff so if anyone has any solutions, please write back to me with instructions that are written as if you're talking to a complete idiot


1. Select cell A1 in the sheet, and insert the current date by pressing Ctrl+;.
2. Select Home -> Number Format dropdown list (in Number Group) -> Short Date or Long Date
Or press Ctrl+1, select the Number tab, and then select Custom.
3. Clear the Type box.
4. Enter the date format in the Type box, based on to the list of symbols below:
m (Month): Displays the months number, without a leading 0 if the number is lower than 10.
mm (Month): Displays the months number, including a leading 0 if the number is lower than 10.
mmm (Month): Displays the first three characters of the months name.
mmmm (Month): Displays the months full name.
mmmmm (Month): Displays the first character of the months name.
d (Day): Displays the days number, without a leading 0 if the number is lower than 10.
dd (Day): Displays the days number, including a leading 0 if the number is lower than 10.
ddd (Day): Displays the days name as a three character text.
dddd (Day): Displays the days full name.
yy or y (Year): Displays the last two digits of the year.
yyy or yyyy (Year): Displays the full year.


I have a spreadsheet that has column of values with a fixed number length of six digits. I wanted to add a leading zero to those numbers. In the past with Excel 2002, I believe I did a format cells, custom format, and put 0###### into the type field.

That doesn't seem to work with Excel 2010, or I'm remembering the process wrong. Eventually this file will get converted into a CSV and used as an import source for another program.

Any thoughts why this isn't working?

Thanks in advance.


I have a text column of 7 to 9 digits in length. Some items have 2 leading zeros and some do not. My formula in other areas will not work on the items without the 2 leading zeros. I know how to Custom Format to get them BUT some of the 5 digit items look like this (2BE21, 25E51, etc.). If I use 0000000 in custom format it coverts those into Scientific. I can't seem to find a way around it. Please help.

I have a spreadsheet containing a column of dates which I use as the data source for a mail merge. I need to convert the dates in this column from the format "18/02/08" to the format "18 February 2008" using Format - Date. Problem is where the dates are missing the leading zero, Excel will not change these e.g. 3/2/08.
Converting to text first did not fix it.

I'm trying to figure out how to format cells in order to end up with single digit numbers with a leading zero. For example, in column A I have the number "1". In B there's "Apples". Column C is a simple formula that combines the two.

What I want to end up with in column C is "01.0 Apples", "02.0 Oranges" etc. but I can't figure out how to preserve the leading zero. I need that since ultimately these values in C are pasted into another file that's sorted alphabetically so I need to leading zero to remain for the single digit entries.

I set a custom format for column A using "00.0" but can't figure out how to get the leading zero to show in column C. Hopefully the attached explains it better than I can!

I need the leading zero to be in the cell when you click on it. Formatting the cell using special or custom are not suitable as they display the leading zero but when you click on the cell it does not contain a leading zero. Formatting the cell for text or putting an apostraphy before the 0 are not suitable either. This is because the file has to be imported into an application that will not accept formats of this type. Is there any other way?

When I format cells for standard 5 digit zip codes, the leading 0 is displayed in the cell but the leading 0 is not displayed in the formula bar.
When I merge to MSWord to create labels, the leading zero is missing.
Can anyone help?

I'm using Excel 2003. I need to create an importable shipping address file
for our UPS shipping system so we can import all the information at once
without typing all 200 addresses by hand. The file to be imported must be
saved in a .csv (comma separated value) format. Many of the zip codes in this
file having leading zeros (New England areas). When I save the .xls file as a
..csv file, the .csv file does not show and/or retain the leading zeros. The
result is that the imported file has a bunch of 4 digit zip codes that get
rejected by UPS and have to be painstakingly hand-corrected.
I have tried virtually every cell format available that makes any sense (and
some no sense!). NOTHING seems to work.


Okay here is the problem. I am working with UPC codes, many of which have a leading zero. Excel is formatting them as numbers, and while I can play with the formatting to make the zero appear, in the formula bar the value is still shown without the zero. Since this is a UPC code it shouldn't even be treated as a number, but as text; however changing the format to text immediately removes the leading zero. The same thing happens if I use "Text to Columns" - it removes the leading zero because the actual value has removed it, just it's being formatted to display one. I need the value to have the leading zero, not the cell.

There are some 25,000 items here. Is there *any* way to change the *value* to text AND keep the leading zero? This data is to be imported into SQL Server so it needs to be text, but just changing the format still removes the leading zero, which will make the data invalid.

Hi - I want the date to be in the following format in a cell. mmddyy. No dashes or slashes. I tried just for an example type in today's date 010208 and the result i got was 10208. I tried formatting the cell with custom format with 000000. It did put the leading zero in there but when I attempted to combine the cells, the zero was dropped.

I'm running into the same issue with the time as well. 01:15:00 when combined with other columns, it drops the leading zero and looks like 11500. Any help would be appreciated.



I am downloading an online phone bill in a csv format. When opening the file, the phone numbers drop the leading 0. Example phone number is 0400123456.
This number is displaying as 400123456. I need this in a number format so I do not want to convert to text.
I know that if I can convert the format to 0400 123 456 (grouping the numbers with spaces between) then excel leaves the zero in place, but how do I do that without going into each cell individually and re typing it?
If I use custom format with a 0### ### ### then the formula vlookup will still read the cell as not having the 0 and i need to use vlookup.
My goal is to have a number format with the leading zero and grouped with #### ### ###
Help please as I am stuck

Hi all,
I'm very very new to programming concepts so bare with me.

I have a column of dates(100+ sometimes). I make a copy of the column so I know what the dates are.
Then, I select the entire column, right-click and go to format cells. Then I convert the entire column to text format. Some of the dates gets converted to datevalue number. I go back and manually type in the dates from the original copy(cell by cell). This is very tedious/time consuming and plain ol boring.

Couple of points to consider is:
I need is the row has to be in text format.
I can't use "custom format" for the cell like dd-mm-yyyy
I can't change the regional settings to UK in control panel.
I can't have slashes eg. dd/mm/yyyy it won't work
The dates should display as dd-mm-yyyy again in text format.

It's a peculiar situation since i'm transfering this file to another program and that programs accepts this crazy format.

Is there a way to automate this process??

Thanks much...

Hi all, I have a spreadsheet with many leading zeroes-I used custom format to do this. Total digits is always 11 but i need to have only first 6 from left. The formula =LEFT(A2,6)is not working because of the leading zeroes...see attached file... Any help will be much appreciated.. Thanks in advance.

I have generated an Excel file from one of our applications, and it has a
field for a date with a format of 9-2006. I need to put a leading zero in
front of the month so it sorts correctly. Is there an easy way to do this?
I tried to set a custom format to it. It doesnt seem to take until I click
off of it and back on it, then it updates it and works. i have to do it to
8,000 rows and it would take forever.


Scott T.


I'm using Excel 97 SR-2 on a Windows XP machine with the date format under
Regional Settings set to dd/mm/yyyy.

I have a simple line chart (on a chart sheet) drawing data (date on X-axis
and data on Y-axis) from three different worksheets, and am experiencing a
problem with the dates on it randomly changing format from dd/mm/yyyy to
m/d/yyyy. This is despite the fact that the dates in the worksheets are in
dd/mm/yyyy format, and I have formatted the chart X-axis in that format
also. Interestingly, if I click from the chart to one of the worksheets
then back to the chart, the dates "correct" themselves (ie, show in
dd/mm/yyyy format again).

If anyone can provide an explanation, and preferably a 'fix', I would love
to hear it.




I'm trying to format a social with leading zeroes. I want the field (when exported) to continue to be a number, so that it can be reimported, so witching it to a format function won't work. I tried putting "@@@-@@-@@@@" in the format property, which adds the dashes, but does not keeping the leading zero.

Any help appreciated!!

We get a file in XML every month, which strips the leading zeroes from our 9-digit ID number. So we have to re-format to add back in the 0s. We've tried all the regular tricks (using Custom Format, formatting to text, etc.) but XML removes all the formatting from your file. Is there a trick to getting the XML file to keep the leading zeroes?