[OP]
Contributor
Anyone know why when I try and copy a date from one excel Workbook (Not from worksheet to another within the same workbook) to another the date is different. From the original the date is entered as 12/31/2005 and when it's copied it changes to 12/30/2000. Anyone have any idea why? Got me puzzled.
Pete
In Memoriam
Ghost in the machine???
A couple of things I can think of that might be worth checking. Were both workbooks created with the same version of Excel? Or is the date field in the original being calculated based on a macro or formula entry in the cell?
Use the fence Luke
[OP]
Contributor
Originally Posted by Doug Shepard
Could be different versions, how would I check that?
Member
Peter,
Look under "HELP" on the top bar, then down to "About MS Excel".....this will tell you the version.
also;
check the column or row in the original file for any macros or the formatting style (as mentioned by Doug)......
In Memoriam
If you do find they were created in different versions of Excel you should be able to make them at least compatible by using the "Save As.." option. When the dialog box pops up, the list box at the bottom should allow you to choose older versions of Excel as well as the normal default choice - which will be the Excel version of the software on your machine. Find the older version workbook and save it in the newer format. You can go in the other direction (saving as an older format) but at the possible risk of losing data/formatting/macros, etc. that weren't available in the older version.
[OP]
Contributor
Roy,
This will just tell me what version of Excel that I'm using not what the file was made with. As far as macros and formatting there isn't any that I know of. I've done several save as and still get the same results with this file.
Member
Can't think of why that might happen (aside from what's been mentioned). To resolve, Instead of Edit_Paste, use Edit_Paste Special then select Values and click OK.
In Memoriam
I don't have Excel here on my home machine, but I'm pretty sure somewhere in the Properties or Statistics box, there's an indication of what Excel version created the workbook or sheet.
[OP]
Contributor
Lars,
Didn't make a difference. What I just tried was to change the date to it's corresponding number. I used 12/12/2000 and the number I get is 35410. I did the paste special thing and in a new Workbook I get 35410 but in the date format it's 12/11/1996. All the other dates were off a day and 4 years. Must have been a Excel version used to make the old Workbook from before the changes made for the millenium.
Member
Aha! On each workbook, go to Tools->Options and click on the Tab "Calculation." Make sure that the box labelled "1904 Date System" is UNchecked. That should fix the problem.
Erin
For all your days prepare and treat them ever alike. When you are the anvil, bear; When you are the hammer, strike.
[OP]
Contributor
Erin,
That's what it was. I wasn't the original creator and I believe the original was done over 10 years ago but my version of Excel had updated it but I guess it checks this if it's a older version.
Thanks everyone for all the help. For those who hate off topic threads, this BB is one of the fastest and most reliable souce of info for Off topic stuff I know of. Not bad for Woodworking info too.
thnaks again, Pete
[OP]
Contributor
Now that I know what's wrong with this Workbook, is there a way to convert it so the dates will be in the same format? If I uncheck it all the dates in this workbook are wrong now. Is there a easy way to do it?
Member
I don't think there's an easy way. Are thre a lot of dates already in the workbook you're copying to? If not, your best bet may be to use the 1904 date system in both workbooks. The newer versions of Excel default to the 1900 system, but I don't think there's any compelling reason not to use the 1904 system.
Until 10 years from now when someone tries to copy the dates into yet another workbook and wonders why they are having problems.
Otherwise, depending on how the worksheet is set up, you could use a formula to add 4 years and 1 day to all the dates; e.g., if cell A1 contained a date that you needed to change, the formula would be =A1+1462
If your dates are all in rows that would work fine; if they're scattered all over, it would be more difficult. I'd really need to see how the worksheet is set up in order to offer the best solution.
Erin
For all your days prepare and treat them ever alike. When you are the anvil, bear; When you are the hammer, strike.
[OP]
Contributor
Erin,I don't think there's an easy way. Are thre a lot of dates already in the workbook you're copying to? If not, your best bet may be to use the 1904 date system in both workbooks. The newer versions of Excel default to the 1900 system, but I don't think there's any compelling reason not to use the 1904 system.
Until 10 years from now when someone tries to copy the dates into yet another workbook and wonders why they are having problems.
Otherwise, depending on how the worksheet is set up, you could use a formula to add 4 years and 1 day to all the dates; e.g., if cell A1 contained a date that you needed to change, the formula would be =A1+1462
If your dates are all in rows that would work fine; if they're scattered all over, it would be more difficult. I'd really need to see how the worksheet is set up in order to offer the best solution.
Erin
Not that many but they are scattered. What I need is a macro so I can assign it to a button that I could click on after clicking the cell with the date in it. I haven't done any macros in a while so I'll have to think about this for a while. Thanks againg for the help and ideas you mentioned above.
Pete
[OP]
Contributor
Here's the macro I used to fix the dates with. Only had about a dozen in 5 worksheets to do. Not pretty but it worked.
Sub DateFixer()
aa = Application.ActiveCell
aa = aa + 1462
ActiveCell = aa
'MsgBox (aa)
End Sub
Posting Permissions
Reply With Quote