Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


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 Video Tutorials

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







Hi,

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

Help?!


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??????????????


regs,
aasim




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

End result:
01-01234-001
14-01234-002

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?

Thanks!

Dave


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??


hi,

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?

Thanks
CoachBarker

Hello:

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

Thanks,
K




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.



Hello,

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.

-Ian


Hello all,

Hope someone could help me as this issue has been driving me insane this weekend.

I import reports into excel that gives me dates in a column. However with some of the dates, such as 02/01/11, there is clearly a 2 leading spaces at the start of the cell. With dates in the format 02/01/2011 it gives me one leading spaces.

I have searched on mr excel and other resources and have tried everything from using TRIM, CLEAN etc to remove them. Those functions just give me a number string. When I try to convert it back to a date it does not work.

Strangely though, this issue of leading spaces does not seem to be produced on import. I tried inserting 02/01/11 into a fresh sheet, and it gives me an extra leading spaces again. Is this normal? It does this for every date from 02/01/11 to 02/12/11. Upon 02/13/11 it does not put leading spaces.

I am finding this really bizarre and is driving me nuts! Could anyone give me a hand with this, Many thanks in advance.

p.s I use excel 2007 & vista


I have data with birthdays that I want to change into a mmdd format. What I have tried to do is change the date format (format cells) to add the leading zero, and this is how it looks, but when I click on the cell, the formula bar does not show the leading zero. This becomes a problem when I use "Text to Columns" to eliminate the birthyear. If the month and day were both two digits, I could use the "Fixed Width" option. Is there a way to keep the leading zero? Also, how do I copy the data to a new cell without having the format of the new cell be a date format? I want to use the mmdd to be part of a user password, and it is not recognized if the cell is in date format.

Thank you for your help!

John


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.

HELP!



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.

Thanks
Jim


Hi,

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.

TIA

Scott T.



Hello,

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.

TIA

Regards,

John