Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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 ...
Get the Current Date in Excel
How to get the current date using Excel.  This method updates the date every day so that it is always accurate. To ...
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 ...
Dates in Excel Explained
I'll explain how dates in Excel work and then point you to many useful tutorials on how to use dates and also time ...
Print the Current Date or Time in Excel
How to print the current date and or time on an Excel spreadsheet. This is a simple technique that allows you to di ...
Increment a Date By X Days for Each Row in Excel
How to quickly increment a date by a certain number of days for each entry. Type the first date in a cell. Make sur ...

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


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?


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!



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





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?


Hi everyone,

I'm generating a report on an oracle based software which then I export to Excel. Unfortunately the dates come out very messy. I've included an .xls file with the dates. They are untouched. As you can see, some are left aligned some right aligned. Nevertheless, all dates are of the format dd/mm/yyyy with zeros (0) automatically omitted - this is how they're shown on the Oracle report, I've changed nothing.

I want all the dates on column B to have the same format as the date on cell D3. Of course, the format must remain dd/mm/yyyy.

Please feel free to ask me questions if any of this is confusing.

Thanking you all in advance.

Regards,
Besian


Hello,

I am using MS Excel 2007.

When I format cells to a "Custom" format, to retain a leading zero, my cells are reverting to "Special" rather than "Custom" and rather than English, it is Chinese.

This is only showing on the format screen; my text is English and my leading zero is present in my spreadsheet.

I only have English as an "Enabled Editing Language." English is set at the "Primary Editing Language."


I received a file that has dates listed as yyyymmdd and I can't get excel to recognize this format as a date so I can do date calculations on them.

Here is what I've tried:
Under "format cells -> number -> date" I tried a few different formats and just get "#######" back which is logical since they aren't serial numbers.

I also tried: "format cells -> number -> custom" input a new type of date format of yyyy/mm/dd and when I apply the new format to my column i just get "##########" back.

What am I missing? I have 190K rows so would rather not have to do a calculation to create the date but if that is the only way to do it I will....

Thoughts? Thanks!


How can I get the mm/dd/yyyy format to stay permenantly in my
Format>Cells>Dates list? I keep setting it up in the Custom dates, but then
it disappears from file to file.

Thanks



Hi there, I have a CSV file and a .xls file. I have VBA that open CSV copies data and pastes it to .xls. Most of this works fine except when I paste data with leading zeros. When I open CSV in text editor the leading zeros are there. When I open CSV in excel they are missing. When I try to copy and paste the leading zeros are removed. The source data with the leading zeros is not always the same length so I cannot use the custom format option to fix it to 8 digits so it always has leading zeros. I did this with my zip codes and that works because they are always the same.

Here is the code where I copy and paste which VoG was so kind to help with:

Windows("BookingConfirmation.csv").Activate
Workbooks("BookingConfirmation.csv").Sheets("BookingConfirmation").Range("A29").Copy
Windows("BookingConfirmation.xls").Activate
Workbooks("BookingConfirmation.xls").Sheets("BookingConfirmation").Range("D18").PasteSpecial Paste:=xlPasteValues


The CSV file is generated by another process I have no control over so I cannot alter the CSV file but I do control the .xls so I can do whatever I need. Thank you for your assistance.


Good Afternoon,

I am trying to work with a .CSV file using Excel. I am experiencing difficulty with the default settings when the file is opened.

The default numerical format is set to "GENERAL." Unfortunately, this format erases all leading zeroes (I'm sure it's trying to be helpful...) in my data. So a cell that should read '0040056" instead reads "40056."

I try to format the data back to "TEXT" instead of general, but the leading zeroes do not come back.

Is there a way to open a .CSV file with a different default number format?


...and I use the term "native" for want of a better term.

I have a User Form with a MultiPage control. On different pages I have datePickers. The datePickers are set to a Format dtpCustom and the CustomFormat is "yyyy.MM.dd". This is the same for all the date pickers on all the multipages.

In the initialization, I set all the dates to Now(), switching between MutliPage.Value = x to set them all (as you have to make the page active to access the datePicker control).

Form loads and shows the dates in the format that I want. However, when I select a date, click the button to execute the code, going to any MultiPage page AFTER that finds all the dates reverting to "dd/MM/yyyy".

I have tried re-initializing the date pickers AFTER performing a procedure including hardcoding them

Code:

frmName.foo.Format = dtpCustom
frmName.foo.CustomerFormat = "yyyy.MM.dd"
frmName.foo.Value = Now()


Does anyone know why I am losing the custom formatting on the date picker control?

Thank you for your time.

Jim


I have a spreadsheet with some values containing leading hyphens. When I save this spreadsheet as a .csv file, all values with leading hyphens show the #NAME error.

For example, I have the value "-050-BLK". When I save as a .csv, excel turns that value into "#NAME". I want to keep the original value, including the leading hyphen in the cell.

I understand that the .csv format strips a sheet of it's formatting much like notepad does when copying and pasting. Is there a way to get around this so that the .csv will keep my original values and stop reading the hyphen as a different sort of dash or formula precursor?

Please advise...


Hi,

Is there a way I can convert dates to text in a CSV file (opened via excel) using a macro?

Currently I have excel recognised dates in the first column of the format dd-mm-yyyy and a corresponding values in the second columns.

I know once I have the file open I can enter the following formula in an adjacent coloum "=text (A2, "MMM-YYYY") but I need to change the format in the existing date feild and not create a new coloum. Sure I could copy and paste the new formatted dates into the cell, but in terms of what I need to do, this is not practical as I several of these CSV files that I need to query and extract data from daily using a macro.

The macro is not currently working because the date format is wrong. I need it to appear as MMM - YYYY. When you view the date in excel it must appear like this (as it would appear in say microsoft word) and not with the underlying date format.

Many Thanks

Elvis


Something tells me this may be simple, but who knows. I've got a sheet with dates listed in a column as mm/dd/YYYY OR YYYY format, but need those dates to be constrained to just the YYYY portion. Is there a formula I can use for this? I know excel doesn't natively include just a YYYY format.

Thanks in advance!


Really stuck here. I have an excel s/s of data with dates in. These dates are formated in custom "dd-mmm-yy" format and I don't really want to change this. I want the user to input the date in an inputbox in dd-mmm-yy format and then the autofilter filter the s/s by this date. Sounds simple but I'm struggling here & the filter is not returning anything. This is my code:

Dim currentd As String
Dim currentd2 As Date

currentd = Format(Now(), "dd-mmm-yy")

currentd2 = Format(InputBox("Date of Rec?", "Valid Format - dd-Mmm-yy", currentd), "dd-mmm-yy")

Range("c2").Select

Selection.AutoFilter Field:=14, Criteria1:="=" & currentd2

End sub

Any help would be much appreciated as the only fix I've found is to convert the dates in the s/s to dd/mm/yyyy format.


Hello

How do i stop a CSV file loosing leading zeros.

when the file is in excel format it is exactly how i want it, but to import
it into another program i need to save it as a csv file and it is then that i
loose the leading zeros

has any body got any ideas

--
Cheers



I'm a long-time lurker and first-time poster.

I have a data column called "Deadline." Sometimes, the deadline is only a date, a time or both. I would like to enter only the data required (date, time or both), and I would like to format the cell as follows:

date only
ENTER: 10/1/07
FORMAT SHOWS: Mon, 10/1/2007

time only
ENTER: 5 p
FORMAT SHOWS: 5:00 pm

both
ENTER: 10/1/07 5 p
FORMAT SHOWS: Mon, 10/1/2007 5:00 pm

Is there a way to set up a custom number format to do this? When I use "mmm, m/d/yyyy, h:mm am/pm" and enter the date only, it automatically adds midnight as the time.

I'd prefer to use custom number format, if possible, but I'll happily use a formula if necessary. I cannot use VBA because I have to share the spreadsheet and it's a nightmare in our company for various reasons.

Thanks in advance for any insight or help.

David


Anyone know if a wildcard can be used in Custom Formatting using text leading off. Example:

U01234567

I have the above custom formatted as: "U"00-00-0000 which gives me U01-23-4567. This give me the format I need however; the U is not always constant and needs to be changed to different letters as required. Maybe custom formatting is not the way to go so if you know of a vba code that will accomplish this I would appreciate it if you can share it with me. Thanks


I am new to the forum and would consider myself a beginner at excel but I'm not a complete idiot either. I need to subtract a custom cell format of m/d/yyyy h:mm Subtracting Column A from Column B to give me a total time. The scope of what I want to do is determine how long a service ticket was open before it was closed. More extensively I need to account for only business hours discluding 7pm to 7am tue-thurs and 7pm-7am fri-mon. Both column's A and B are custom cell format m/d/yyyy h:mm.


Hello - I couldn't find the answer through searching so here is the open question. We have an Excel spreadsheet where we need to preserve the leading zero's and text of the number as this is a UPC field where the zero's are significant to another vendor we work with. This is MS Office Excel 2003 SP1. The column where we cut and paste has been designated to "text" format.

There are two issuue we are struggling with here and cursing Excel along the way!

1. Cut and pasting UPC data that has no leading zeros from other origins results in the cell reverting our text to scientific notation. In this case it happens when the data is pasted directly into the cell What we found is that if we paste the data into tthe F(x) field that it does paste correctly as text. Why doesn't a direct paste of the number respect the cells "Text" format?

2. Cut and pasting UPC numbers directly into the cell that has a leading zero seems to preserve the number as text but it removes the leading zero. If we paste the same data into the F(X) area it will preserve the zero and put a warning item on the cell that the data was text or rpreceded by an apostrophe.

The UPC numbers being tested a

081227828820
886970114820

My goal here is to allow my colleagues to just cut and paste the numbers as they get them and not have to worry about Excel mucking with them. I would expect that if I designate a column as text it should remain as text in the original form. Sigh.


Hi All,
I have been given a spreadsheet that has been updated by a number of different users over time. The problem is the date format used by some is different then others.
I would like to have all the dates in the format DD-MON-YYYY if possible.
I tried the text to column option and 2009 May 01 would come out as 20 September 2008
after I selected DMY.
Is there any VB code that can look at each cell in a column, interperate its correct date and put it in the DD-MON-YYYY format if it is not that already?
Thanks
Novice


I have a worksheet that includes a 4 digit code and an 8 digit code, both of which can have leading zeros that must be included. Ex: 0421, 00216784. To retain the zeros I used custom formats "00000000" and "0000". Excel automatically changed these formats to Special, Greek each time. The numbers displayed as they should but they couldn't be exported outside of excel. I found a workaround using the TEXT(cell, "00000000") formula, however I'd like to know why it won't work for me and if I'm doing something wrong or if this is built into Excel. I'm especially curious if there is some reason why it should do that, ie Greek is somehow related to leading zero number formats.

Thanks!


I am opening a csv file in excel with many columns in the US date
format (mm-dd-yyyy). I need these all to be in the UK format
(dd-mm-yyyy). When I run text to columns on each column individually I
can choose the column data format MDY and it will reformat the dates
correctly. However when I recorded a macro to do this it doesn't work.

Does anyone have any idea why this works when you do it manually but
not via a macro.

Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
FieldInfo:=Array(1, 3)

and repeated for each column

Thanks,
Andy




I have imported some data which is a series of payroll numbers &
names, of which some numbers have leading zeros, ie 0034 or 0187,
which are stored OK, but if I amend any of the numbers, I lose the
leading zeros, and the data is rejected by a vlookup field in another
sheet, i.e. 0034 becomes 34, which vlookup does not recognise.
I have changed the cell format code to '0000' and although it shows as
0034 in the actual cell, the value is still 34 having 'lost' the
preceeding noughts.
Even if you click into the formula bar when a cell is selected, it is
sufficient to somehow change the way in which that data is stored, and
changes it from 0001 to 1, when viewing the value in the formula bar.

I understand that this can be corrested by creating another column with the formula =TEXT(A1,"0000") but it would be difficult for me to do this.
Does anyone know why this happens and have a better solution?