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 -