Home |
Search |
Today's Posts |
#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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Printing Problems... | Excel Discussion (Misc queries) | |||
Copy a column in worksheet with a character change | Excel Worksheet Functions | |||
how do i copy formula and change worksheet instead of cell | Excel Worksheet Functions | |||
how do i make a date change automatically if i change one before . | Excel Discussion (Misc queries) | |||
Formating Dates for production schedule | Excel Discussion (Misc queries) |