I have some VBA code that is importing a file from another source. The vendor who created the source file did not take into account how excel treats the 2-digit year. As a result, they are sending me dates that occurs in the year 19xx when it needs to be 20xx. I need to convert any date with a year from 1930-1999 to year 2030-2999. The dates are in column E and I want convert to values. Any assistance is appreciated. Thanks
Please Login or Register to view this content.
Last edited by maacmaac; 11-09-2011 at 04:55 PM.
maacmaac,
Give this a try:
Please Login or Register to view this content.
Hope that helps,
~tigeravatar
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
That does work but it doesn't quite solve the issue. I should had explained better: The file contains dates for both 19xx and 20xx. Anything with an original date of 2030 to 2999 was converted from 20xx to 19xx. Any dates with from 2000 to 2029 are correct.
So there is a mix of dates. I only want to change dates with year 19xx to 20xx. If the date already has 20xx, then do nothing. Thanks.
maacmaac,
Updated code:
Please Login or Register to view this content.
You could change your regional settings to control the interpretation of two-digit years. See http://support.microsoft.com/kb/214391
Entia non sunt multiplicanda sine necessitate
I was thinking of doing that but the spreadsheet is going to be distributed to other users and it may not work on their machines.
Ended up checking to see if year is less than 2000. If it is less than 2000, then perform action, else do nothing. Thanks for all the comments.
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks