Yesterday a vendor sent us a giant Excel spreadsheet that we’ve been uploading into a database. Everything went without a hitch, except for the date fields. Because of the strictures of our database, we need to upload them in the text format “01012003,” with the first two digits the month, the next two the date, and the last four the year. Unfortunately, somehow when the vendor sent the file to us, the first zero got clipped off of many of the dates, presumably because the cells were mistakenly formatted as numbers.
Since there are a few thousand of these cells, I’d like to re-attach a leading zero to these without having to do them manually. But I have no idea how to write a cell formula that would do that. Any suggestions, Doper Excel experts?
Highlight the cells you want to change, go to ‘Format’, click ‘Cells…’, click ‘Number’, highlight ‘Custom’, and type in a bunch of zeros in the box marked ‘Type’ until you have a leading zero.
Can’t you just format them as dates? If the choices provided by Excel don’t give you a ddmmyy format, but I think they do, you can just add it in as a custom format.
Go to your vendor’s spreadsheet, highlight the column with the dates, right click, and select Format Cells. Change it to a text field. This way, Excel will not attempt to reformat the cell and take away the leading zero.
If the zeroes are already gone, you can insert a column in front of the date, populate all the cells with just a zero, and concatenate the two cells together. For example, if you have the date 1012003 in cell B5, put a zero in cell A5, and in C5, type in this formula:
=(A5)&(B5)
Then highlight column C and Copy, Paste Special, Values. That should do the trick.
Adding a zero to the front of each item will not work because two digit months do not need one. Here is what you can do:
Convert the date column to text in the format menu.
Insert a column next to the date column.
Put the following formula in it. =RIGHT(CONCATENATE(0,A1),8)
*where A1 is the first date cell.
** This puts a leading zero in front of all the dates but then reads back from the right only the first 8 digits. This is the missing component from the previous replies.
Fill down for all date cells
Select the new cells - Copy - Paste Special (Values) to make the cells independent of the formula
Actually, Jpeg and Shagnasty, the specific database upload I’m running does require the leading zero for the date field. (That’s where we ran into the problem, I think: the vendor didn’t know that.)
Thanks for all the suggestions. I think I’m going to go with the concatenation formula suggested by interface2x and Lord Ashtar, as I’ve used similar formulas before.
In the future, when you import sheets from other formats, you should get a screen in the import wizard asking you to pick a format for each column… in the case of your dates, you should have picked text. Seeing nothing but numerals in that column, excel defaulted to number for that column.
The leading zeros may still be there. Select one of the cells with a date of Jan thru Sep and look in the entry box at the top of the window. Is the zero still there?
If so, then you can use Surreals suggestion. Otherwise, Shagnasty’s will work.
Another tip, to force text formatting in a cell, is to lead with an apostrophe… for instance, if you are entering 021004 and want the leading zero to remain, then enter '021004 to tell Excel that this data is a string and not a number. That apostrophe will always be part of the data in the cell, but it will not be displayed by Excel.
Well, I just used interface2x and Lord Ashtar’s suggestion, and it worked. I’ve got the file uploading now.
bughunter: Yeah, I know about the problem with ensuring leading zeros occur–we face the same problem with zip codes from New England. Somehow, though, our vendor did not :rolleyes: Thanks for reminding me about the apostrophe gambit, though–back in the old, old days of Lotus 1-2-3 version 1a, that was the only way you could get the spreadsheet to force text formatting. I didn’t realize Excel had carried it over.