I am importing a large excel file from with ~2500 line items. I am having issues with excel formating some of the dates to a year 19xx format instead of a 20xx format.
For example: some of the values that are being copied are 3/15/43 or 9/22/47. Then value is being converted to 03/15/1943 and to 09/22/1947. Is there an easy way to convert these value back to a 20xx format. I tried using the custom button with "mm/dd/20yy" but didn't work. Thanks for any comments.
Last edited by maacmaac; 04-22-2010 at 08:32 PM.
Entia non sunt multiplicanda sine necessitate
Link helped a little but couldn't quite figure how to incorporate. The problem is that the data I am getting from another spreadsheet is already messed up (i.e. there are dates that are formatted as 19xx and they shouldn't be)
I did come up with a work around that I think might work. I am going through each cell to determine if it is less than 01/01/2000. If it is less than 01/01/2000, then add 100 years to the year.
The code I tried using is as follows but can't figure out why it won't work. Not sure if the "Date" function is available in VBAThanks for any comments
Please Login or Register to view this content.
Think you wanna subtract 100 years:
Please Login or Register to view this content.
Shg, thanks for the great tip. I actually need to add 100 years. If the current value of a cell is 09/18/1947, I need it to convert to 09/18/2047. I changed code to add 100 and ran it through a number of examples and it appears to be correctly (also had to change the "#1/1/2010#" portion to "#1/1/2000#"). Thanks again for your assistancePlease 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