Home |
Search |
Today's Posts |
#1
|
|||
|
|||
year changes when copied from one workbook to another
I receive a file from one department and copy the information into another
file. When I do this the date changes. It adds one day and four years to the date. The options has the 1904 date system selected. When I turn this off the dates come in correctly but the rest of my dates in the worksheet change. What can I do? |
#2
|
|||
|
|||
Hi Pgarbarini
Use -1462 in the date cell after the date -- Regards Ron de Bruin http://www.rondebruin.nl "Pgarbarini" wrote in message ... I receive a file from one department and copy the information into another file. When I do this the date changes. It adds one day and four years to the date. The options has the 1904 date system selected. When I turn this off the dates come in correctly but the rest of my dates in the worksheet change. What can I do? |
#3
|
|||
|
|||
Saved from a previous post:
One workbook was using a base year of 1900 and the other was using 1904. (tools|options|calculation tab|1904 date system) One way to add those four years back is to find an empty cell, put 1462 into that cell. Copy that cell. Select your range that contains the dates. Edit|PasteSpecial|click Add (in the operation box). You may have to reformat the cell as a date (mine turned to a 5 digit number). But it should work. You may want to do it against a copy...just in case. (I'm not sure which one you'll fix. You may want to edit|pastespecial|click subtract.) Most windows users use 1900 as the base date. Mac users (mostly??) use 1904 as the base date. Pgarbarini wrote: I receive a file from one department and copy the information into another file. When I do this the date changes. It adds one day and four years to the date. The options has the 1904 date system selected. When I turn this off the dates come in correctly but the rest of my dates in the worksheet change. What can I do? -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
year changes when copied from one workbook to another
Why is this happening in the first place? It wasn't happening before to me,
then all of a sudden it started. Also, why should you have to input extra numbers on dates, etc,, which I don't fully understand what you are saying to do any way, just to copy dates from one worksheet to another? Isn't there a "fix" for this? I am confused, and would really like to get it fixed because I copy dates between worksheets all the time for work. Thank you. "Dave Peterson" wrote: Saved from a previous post: One workbook was using a base year of 1900 and the other was using 1904. (tools|options|calculation tab|1904 date system) One way to add those four years back is to find an empty cell, put 1462 into that cell. Copy that cell. Select your range that contains the dates. Edit|PasteSpecial|click Add (in the operation box). You may have to reformat the cell as a date (mine turned to a 5 digit number). But it should work. You may want to do it against a copy...just in case. (I'm not sure which one you'll fix. You may want to edit|pastespecial|click subtract.) Most windows users use 1900 as the base date. Mac users (mostly??) use 1904 as the base date. Pgarbarini wrote: I receive a file from one department and copy the information into another file. When I do this the date changes. It adds one day and four years to the date. The options has the 1904 date system selected. When I turn this off the dates come in correctly but the rest of my dates in the worksheet change. What can I do? -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
year changes when copied from one workbook to another
It's because a date in Excel is a serial number So in Windows Jan 0 1900
then every day after that gets incremented by 1. You can check that by taking a date and format it as General, for instance today's (Nov 19th 2008) Excel date is 39772. For Excel for Mac the dates start on Jan 1 1904 thus the difference and since Excel for Mac came out before Excel for Windows MS couldn't use the same date system only if they wanted to compete in spreadsheet programs since lotus was the big shot then. -- Regards, Peo Sjoblom "clinhart" wrote in message ... Why is this happening in the first place? It wasn't happening before to me, then all of a sudden it started. Also, why should you have to input extra numbers on dates, etc,, which I don't fully understand what you are saying to do any way, just to copy dates from one worksheet to another? Isn't there a "fix" for this? I am confused, and would really like to get it fixed because I copy dates between worksheets all the time for work. Thank you. "Dave Peterson" wrote: Saved from a previous post: One workbook was using a base year of 1900 and the other was using 1904. (tools|options|calculation tab|1904 date system) One way to add those four years back is to find an empty cell, put 1462 into that cell. Copy that cell. Select your range that contains the dates. Edit|PasteSpecial|click Add (in the operation box). You may have to reformat the cell as a date (mine turned to a 5 digit number). But it should work. You may want to do it against a copy...just in case. (I'm not sure which one you'll fix. You may want to edit|pastespecial|click subtract.) Most windows users use 1900 as the base date. Mac users (mostly??) use 1904 as the base date. Pgarbarini wrote: I receive a file from one department and copy the information into another file. When I do this the date changes. It adds one day and four years to the date. The options has the 1904 date system selected. When I turn this off the dates come in correctly but the rest of my dates in the worksheet change. What can I do? -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
year changes when copied from one workbook to another
Thank you for the explanation - helpful info. However, how do I fix the date
system on my Mac and my PC's that I have to transfer files between daily to get this to work? Is it somewhere in Excel? I can't find it on the Mac - I am using Office for Mac 2008. Thanks. "Peo Sjoblom" wrote: It's because a date in Excel is a serial number So in Windows Jan 0 1900 then every day after that gets incremented by 1. You can check that by taking a date and format it as General, for instance today's (Nov 19th 2008) Excel date is 39772. For Excel for Mac the dates start on Jan 1 1904 thus the difference and since Excel for Mac came out before Excel for Windows MS couldn't use the same date system only if they wanted to compete in spreadsheet programs since lotus was the big shot then. -- Regards, Peo Sjoblom "clinhart" wrote in message ... Why is this happening in the first place? It wasn't happening before to me, then all of a sudden it started. Also, why should you have to input extra numbers on dates, etc,, which I don't fully understand what you are saying to do any way, just to copy dates from one worksheet to another? Isn't there a "fix" for this? I am confused, and would really like to get it fixed because I copy dates between worksheets all the time for work. Thank you. "Dave Peterson" wrote: Saved from a previous post: One workbook was using a base year of 1900 and the other was using 1904. (tools|options|calculation tab|1904 date system) One way to add those four years back is to find an empty cell, put 1462 into that cell. Copy that cell. Select your range that contains the dates. Edit|PasteSpecial|click Add (in the operation box). You may have to reformat the cell as a date (mine turned to a 5 digit number). But it should work. You may want to do it against a copy...just in case. (I'm not sure which one you'll fix. You may want to edit|pastespecial|click subtract.) Most windows users use 1900 as the base date. Mac users (mostly??) use 1904 as the base date. Pgarbarini wrote: I receive a file from one department and copy the information into another file. When I do this the date changes. It adds one day and four years to the date. The options has the 1904 date system selected. When I turn this off the dates come in correctly but the rest of my dates in the worksheet change. What can I do? -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
year changes when copied from one workbook to another
Read again what Dave Peterson said:
"tools|options|calculation tab|1904 date system" -- David Biddulph "clinhart" wrote in message ... Thank you for the explanation - helpful info. However, how do I fix the date system on my Mac and my PC's that I have to transfer files between daily to get this to work? Is it somewhere in Excel? I can't find it on the Mac - I am using Office for Mac 2008. Thanks. "Peo Sjoblom" wrote: It's because a date in Excel is a serial number So in Windows Jan 0 1900 then every day after that gets incremented by 1. You can check that by taking a date and format it as General, for instance today's (Nov 19th 2008) Excel date is 39772. For Excel for Mac the dates start on Jan 1 1904 thus the difference and since Excel for Mac came out before Excel for Windows MS couldn't use the same date system only if they wanted to compete in spreadsheet programs since lotus was the big shot then. -- Regards, Peo Sjoblom "clinhart" wrote in message ... Why is this happening in the first place? It wasn't happening before to me, then all of a sudden it started. Also, why should you have to input extra numbers on dates, etc,, which I don't fully understand what you are saying to do any way, just to copy dates from one worksheet to another? Isn't there a "fix" for this? I am confused, and would really like to get it fixed because I copy dates between worksheets all the time for work. Thank you. "Dave Peterson" wrote: Saved from a previous post: One workbook was using a base year of 1900 and the other was using 1904. (tools|options|calculation tab|1904 date system) One way to add those four years back is to find an empty cell, put 1462 into that cell. Copy that cell. Select your range that contains the dates. Edit|PasteSpecial|click Add (in the operation box). You may have to reformat the cell as a date (mine turned to a 5 digit number). But it should work. You may want to do it against a copy...just in case. (I'm not sure which one you'll fix. You may want to edit|pastespecial|click subtract.) Most windows users use 1900 as the base date. Mac users (mostly??) use 1904 as the base date. Pgarbarini wrote: I receive a file from one department and copy the information into another file. When I do this the date changes. It adds one day and four years to the date. The options has the 1904 date system selected. When I turn this off the dates come in correctly but the rest of my dates in the worksheet change. What can I do? -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
year changes when copied from one workbook to another
And if you want help on Excel for the Mac, use
news:microsoft.public.excel.macintosh -- David Biddulph "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... Read again what Dave Peterson said: "tools|options|calculation tab|1904 date system" -- David Biddulph "clinhart" wrote in message ... Thank you for the explanation - helpful info. However, how do I fix the date system on my Mac and my PC's that I have to transfer files between daily to get this to work? Is it somewhere in Excel? I can't find it on the Mac - I am using Office for Mac 2008. Thanks. "Peo Sjoblom" wrote: It's because a date in Excel is a serial number So in Windows Jan 0 1900 then every day after that gets incremented by 1. You can check that by taking a date and format it as General, for instance today's (Nov 19th 2008) Excel date is 39772. For Excel for Mac the dates start on Jan 1 1904 thus the difference and since Excel for Mac came out before Excel for Windows MS couldn't use the same date system only if they wanted to compete in spreadsheet programs since lotus was the big shot then. -- Regards, Peo Sjoblom "clinhart" wrote in message ... Why is this happening in the first place? It wasn't happening before to me, then all of a sudden it started. Also, why should you have to input extra numbers on dates, etc,, which I don't fully understand what you are saying to do any way, just to copy dates from one worksheet to another? Isn't there a "fix" for this? I am confused, and would really like to get it fixed because I copy dates between worksheets all the time for work. Thank you. "Dave Peterson" wrote: Saved from a previous post: One workbook was using a base year of 1900 and the other was using 1904. (tools|options|calculation tab|1904 date system) One way to add those four years back is to find an empty cell, put 1462 into that cell. Copy that cell. Select your range that contains the dates. Edit|PasteSpecial|click Add (in the operation box). You may have to reformat the cell as a date (mine turned to a 5 digit number). But it should work. You may want to do it against a copy...just in case. (I'm not sure which one you'll fix. You may want to edit|pastespecial|click subtract.) Most windows users use 1900 as the base date. Mac users (mostly??) use 1904 as the base date. Pgarbarini wrote: I receive a file from one department and copy the information into another file. When I do this the date changes. It adds one day and four years to the date. The options has the 1904 date system selected. When I turn this off the dates come in correctly but the rest of my dates in the worksheet change. What can I do? -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
year changes when copied from one workbook to another
I'm sorry to seem so naive. But I have just switched from PC to Mac. I have
to transfer files between PC's and my Mac daily at work. Excel 2008 in Mac does not have a tools/options menu choice. And I cannot change all of the many PC's that I communicate with. So I cannot go into Excel on the PC's to make the changes. I cannot access the news:microsoft.public.excel.macintosh suggested below because when I try to enter it into browser, it insists upon opening up Outlook Express, which I do not have on my Mac. I have tried looking on other forums but cannot find an answer that I can implement. How can I make the change on my Mac's Excel program? Thank you for your patience. "David Biddulph" wrote: And if you want help on Excel for the Mac, use news:microsoft.public.excel.macintosh -- David Biddulph "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... Read again what Dave Peterson said: "tools|options|calculation tab|1904 date system" -- David Biddulph "clinhart" wrote in message ... Thank you for the explanation - helpful info. However, how do I fix the date system on my Mac and my PC's that I have to transfer files between daily to get this to work? Is it somewhere in Excel? I can't find it on the Mac - I am using Office for Mac 2008. Thanks. "Peo Sjoblom" wrote: It's because a date in Excel is a serial number So in Windows Jan 0 1900 then every day after that gets incremented by 1. You can check that by taking a date and format it as General, for instance today's (Nov 19th 2008) Excel date is 39772. For Excel for Mac the dates start on Jan 1 1904 thus the difference and since Excel for Mac came out before Excel for Windows MS couldn't use the same date system only if they wanted to compete in spreadsheet programs since lotus was the big shot then. -- Regards, Peo Sjoblom "clinhart" wrote in message ... Why is this happening in the first place? It wasn't happening before to me, then all of a sudden it started. Also, why should you have to input extra numbers on dates, etc,, which I don't fully understand what you are saying to do any way, just to copy dates from one worksheet to another? Isn't there a "fix" for this? I am confused, and would really like to get it fixed because I copy dates between worksheets all the time for work. Thank you. "Dave Peterson" wrote: Saved from a previous post: One workbook was using a base year of 1900 and the other was using 1904. (tools|options|calculation tab|1904 date system) One way to add those four years back is to find an empty cell, put 1462 into that cell. Copy that cell. Select your range that contains the dates. Edit|PasteSpecial|click Add (in the operation box). You may have to reformat the cell as a date (mine turned to a 5 digit number). But it should work. You may want to do it against a copy...just in case. (I'm not sure which one you'll fix. You may want to edit|pastespecial|click subtract.) Most windows users use 1900 as the base date. Mac users (mostly??) use 1904 as the base date. Pgarbarini wrote: I receive a file from one department and copy the information into another file. When I do this the date changes. It adds one day and four years to the date. The options has the 1904 date system selected. When I turn this off the dates come in correctly but the rest of my dates in the worksheet change. What can I do? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
year changes when copied from one workbook to another | Excel Discussion (Misc queries) | |||
Playing a macro from another workbook | Excel Discussion (Misc queries) | |||
How to hyperlink from a workbook to sheets in another workbook? | Excel Worksheet Functions | |||
Unprotect Workbook | Excel Discussion (Misc queries) | |||
Stubborn toolbars in Excel | Excel Discussion (Misc queries) |