Remove Leading Zeros from dates, need 4 Digit year

[Unbridled]

Because of a backlog with our .NET programmer, I am working with a
report that was extracted from an SQL database to an Excel file. I
have linked the Excel file to Access to provide reports in a specific
format so that all I have to do is run the extract then overwrite the
existing XLS extract. However, I have noticed that there are
sometimes leading zeros in some of the entries which could be either
in the month and/or the day. Even worse, some years are only two
digits. I need the format to be m/d/yyyy (only 1/1/2009 - not
01/01/09 or any variation thereof) for my calculation in the report to
work. The extract has over a dozen columns with dates I will need to
validate. Any ideas or links so that I can apply to format properly
appreciated.

 

[Pete_UK]

I presume you have tried to apply that format (m/d/yyyy) to the
offending cells - does it not work? If not, then those "dates" are
probably being treated as text values by Excel. If there is nothing
else in the cells (no hidden space characters etc), then you could
enter 1 into a blank cell somewhere and <copy> that cell. Then
highlight all the offending cells and Edit | Paste Special | Values
(check) | Multiply (check) | OK then <Esc>. Then, with those cells
still selected, you can apply the custom format of m/d/yyyy, and then
you can delete the 1 from the original cell.

Hope this helps.

Pete

 

[Unbridled]

I presume you have tried to apply that format (m/d/yyyy) to the
offending cells - does it not work? If not, then those "dates" are
probably being treated as text values by Excel. If there is nothing
else in the cells (no hidden space characters etc), then you could
enter 1 into a blank cell somewhere and <copy> that cell. Then
highlight all the offending cells and Edit | Paste Special | Values
(check) | Multiply (check) | OK then <Esc>. Then, with those cells
still selected, you can apply the custom format of m/d/yyyy, and then
you can delete the 1 from the original cell.

Hope this helps.

Pete



- Show quoted text -

I used your suggestion and created VB code using that logic. Thanks!

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

I used your suggestion and created VB code using that logic.  Thanks!- Hide quoted text -

- Show quoted text -

 

[krashthrills]

Hey there, I just wanted to express my appreciation for that little tip. I used a free trial version of a pdf to excel converter to put some bank statements into a spreadsheet and eventually after inputting many other documents, credit card statements, receipts etc. manually, the combined sheet is to be used as a trial balance encompassing a calendar year. I ran into the same sort of problem discussed here in that the bank statement (former pdf) dates were all in the format with leading zeros and no amount of formatting the cells changed that fact. I had a column of dates that was 1873 rows long with a hodge podge of different looking dates and since I'm being paid a significant amount of money to to produce a trial balance for this company, the least I can do is have the appearance look clean. It wasn't looking clean at all and I was starting to get frustrated. I couldn't figure out what to even really google search for but eventually lit upon the description involving the leading zeros.

I solved my problem using your technique like this. I inserted a 1 with no decimal point into a cell, copied it and then highlighted all of the dates in column a, did a paste special multipy and it converted all of the cells into numbers like 40.544. I then did a right click, format cells, chose the 3/14/01 format hit okay and voila my problem was solved. Thanks again for helping me out I really do appreciate it.

Sincerely,

anthony mandich