MS Excel Date Problems

[Peter Stahl]

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

[Doug Shepard]

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?

[Peter Stahl]

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?


Could be different versions, how would I check that?

[Roy Wall]

Could be different versions, how would I check that?

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)......

[Doug Shepard]

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.

[Peter Stahl]

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)......
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.

[Lars Thomas]

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.

[Doug Shepard]

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.

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.

[Peter Stahl]

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.


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.

[Erin Raasch]

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.

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

[Peter Stahl]

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

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

[Peter Stahl]

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?

[Erin Raasch]

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?

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. :D

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

[Peter Stahl]

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. :D

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

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

[Peter Stahl]

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

[Erin Raasch]

Peter,

I was afraid that there might not be an elegant solution, but at least you found something that worked. Glad to hear you got the problem solved.

Erin

[Peter Stahl]

Peter,

I was afraid that there might not be an elegant solution, but at least you found something that worked. Glad to hear you got the problem solved.

Erin

Erin,

It was easier than I thought. Thank you and everyone else for their help. here's a link to the MS Knowledge Base that explains the 1900 vs 1904 date systems.
http://support.microsoft.com/default.aspx?scid=kb;en-us;180162