If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Why do my dates change when I copy them between Excel worksheets?
I often copy cell contents from one worksheet and post them into a new one.
When I copy one set of dates and paste them into a new worksheet, the date falls back one day and to 2001. For example, an original date of May 30, 2005 copies as May 29, 2001. Our agency's IT guru can't figure it out. I could use some help to end this frustrating error. Thanks. |
#2
|
|||
|
|||
Looks like both have different date system
Change them to follow the same date system by going into TOOLs-OPtions-Calculation and uncheck 1904 date system from both your Windows machines. If using Mac I guess leave them checked "rrjohnsonia" wrote in message ... I often copy cell contents from one worksheet and post them into a new one. When I copy one set of dates and paste them into a new worksheet, the date falls back one day and to 2001. For example, an original date of May 30, 2005 copies as May 29, 2001. Our agency's IT guru can't figure it out. I could use some help to end this frustrating error. Thanks. |
#3
|
|||
|
|||
Hi
One workbook is set to 1900 calendar format (=Windows default calendar). The other is set to 1904 calendar (Mac). You can do this in the Tools Options menu in Windows Excel, and in Excel Preferences (I think) on a Mac Excel. The difference between those standards is 1462 days. You can not end this error in a mixed environment without brute force, but it's easy to convert from one standard to another by selecting the date cell(s) and run the proper macro: Sub Date1900to1904() Dim Cel As Range For Each Cel In Selection If Cel.HasFormula = False Then If IsDate(Cel.Value) Then Cel.Value = Cel.Value - 1462 End If End If Next End Sub Sub Date1904to1900() Dim Cel As Range For Each Cel In Selection If Cel.HasFormula = False Then If IsDate(Cel.Value) Then Cel.Value = Cel.Value + 1462 End If End If Next End Sub HTH. Best wishes Harald "rrjohnsonia" skrev i melding ... I often copy cell contents from one worksheet and post them into a new one. When I copy one set of dates and paste them into a new worksheet, the date falls back one day and to 2001. For example, an original date of May 30, 2005 copies as May 29, 2001. Our agency's IT guru can't figure it out. I could use some help to end this frustrating error. Thanks. |
#4
|
|||
|
|||
Hi Sonia,
Try this : Go to Tools / Options / Calculation and uncheck the box for "1904 date system" under work book options. Thanks, Manish "rrjohnsonia" wrote: I often copy cell contents from one worksheet and post them into a new one. When I copy one set of dates and paste them into a new worksheet, the date falls back one day and to 2001. For example, an original date of May 30, 2005 copies as May 29, 2001. Our agency's IT guru can't figure it out. I could use some help to end this frustrating error. Thanks. |
#5
|
|||
|
|||
I wouldn't do this right aways since one machine will be 1462 days off, put
1462 days in an empty cell, copy it, select the dates and paste special and select add, if going the other way subtract Regards, Peo Sjoblom "N Harkawat" wrote: Looks like both have different date system Change them to follow the same date system by going into TOOLs-OPtions-Calculation and uncheck 1904 date system from both your Windows machines. If using Mac I guess leave them checked "rrjohnsonia" wrote in message ... I often copy cell contents from one worksheet and post them into a new one. When I copy one set of dates and paste them into a new worksheet, the date falls back one day and to 2001. For example, an original date of May 30, 2005 copies as May 29, 2001. Our agency's IT guru can't figure it out. I could use some help to end this frustrating error. Thanks. |
#6
|
|||
|
|||
"N Harkawat" skrev i melding
... Looks like both have different date system Change them to follow the same date system by going into TOOLs-OPtions-Calculation and uncheck 1904 date system from both your Windows machines. If using Mac I guess leave them checked This checkbox clicking will shift existing dates 4 years 1 day, just as the mentioned copying does. Be very careful with this. This whole thing comes from those computers' date systems. 1 is a day, and date cells really contain "number of days since day 1". Problem is "day 1 was when ?". Steve says new year 1904, Bill says four years earlier. HTH. Best wishes Harald "rrjohnsonia" wrote in message ... I often copy cell contents from one worksheet and post them into a new one. When I copy one set of dates and paste them into a new worksheet, the date falls back one day and to 2001. For example, an original date of May 30, 2005 copies as May 29, 2001. Our agency's IT guru can't figure it out. I could use some help to end this frustrating error. Thanks. |
#7
|
|||
|
|||
"Harald Staff" wrote: "N Harkawat" skrev i melding ... Looks like both have different date system Change them to follow the same date system by going into TOOLs-OPtions-Calculation and uncheck 1904 date system from both your Windows machines. If using Mac I guess leave them checked This checkbox clicking will shift existing dates 4 years 1 day, just as the mentioned copying does. Be very careful with this. This whole thing comes from those computers' date systems. 1 is a day, and date cells really contain "number of days since day 1". Problem is "day 1 was when ?". Steve says new year 1904, Bill says four years earlier. Bill even invented a date and started with January 0 1900 g Peo |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Printing Problems... | Peter | General Discussion | 12 | February 6th, 2005 07:37 PM |
change the default copy paste format | MattG | General Discussion | 2 | October 1st, 2004 06:29 PM |
Cut and Copy Behavior - Any way to change? | Colin Higbie | Setting up and Configuration | 1 | March 31st, 2004 03:19 AM |
CHANGE DATES | ME | Worksheet Functions | 1 | February 4th, 2004 12:08 AM |