Re: [libreoffice-users] Re: Paste of Dates Incorrect in Calc
Update, This appears to be the issue. Thanks! The original source for the data in the spreadsheet was from Excel from about 15 years ago. Since then it's been through a bunch of upgrades to MS Office, StarOffice, OpenOffice and LibreOffice on several different systems both Mac and PC and across many operating system revisions. Once I got the base dates set to match in the new spreadsheets the dates work as expected. On the Mac it's changed in Preferences, Calc, Calculate over on the right hand side of the window, in case anyone else is looking for the Tools link mentioned in the help web site. Now I'm trying to figure out if there is a way to write some sort of Macro to change the dates so I can set the day zero to be the now standard one of 12/30/1899 and update my spreadsheets. The volume of them and the legacy data may make it impossible. Anyway, thanks for solving my problem. On May 12, 2013, at 3:09 PM, Steve Edmonds wrote: Hi. Take a look at https://help.libreoffice.org/3.6/Calc/Date_and_Time_Functions To me the difference you are getting looks close to the difference between the date bases for day zero. Steve Eugenie (Oogie) McGuire Desert Weyr http://www.desertweyr.com/ Paonia, CO USA -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: Paste of Dates Incorrect in Calc
Am 12.05.2013 21:00, schrieb Oogie McGuire: In the first spreadsheet I select the cells containing the dates I want. copy (command c) move to the spreadsheet where I want to paste them select the cells where they are to be pasted paste (command v) and the data is entered as a date but they have all changed what was 05/08/2005 in one sheep has become 05/09/2001 in the second sheet! without knowing the reasons of your problem, and in addition what Brian wrote, you could try the following: As dates are represented internally by (integer) numbers, please select the cells containing the dates before you copy them, and set the cell format to standard. Then all dates should turn into numbers. What happens if you copypaste those numbers to your new spreadsheet? Do the numbers change? If not, you can then select the target cells and format them as date - and see if they are correct now. Nino -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Paste of Dates Incorrect in Calc
OK following up on the suggestions. First the user profile page linked to doesn't exist so I can't try that. I upgraded my system to the latest LibreOffice in case that was a problem am running 3.6.6.2 (Build ID: f969faf) All data was originally created on the same machine in the US locale not changed. I have tried all subsets of paste special I can think of. I tried setting the format of the cells to number general. Copied those, pasted into a new spreadsheet, The numbers did not change at all. But when I then set the format to date the dates are not the same and are incorrect. Tried the copy within the sheet where the dates are correct and it works properly Tried a select of the dates where they are correct. get a brand new blank spreadsheet, paste the dates. They are incorrect Tried copy of the dates after they have been changed to format as numbers create a brand new spreadsheet paste verify the numbers are the same change the format to date and then the dates are incorrect. I'd be glad to send the info to anyone else to se if they can figure it out. Not sure how or where to upload a file though On May 12, 2013, at 2:13 PM, Nino Novak wrote: Am 12.05.2013 21:00, schrieb Oogie McGuire: In the first spreadsheet I select the cells containing the dates I want. copy (command c) move to the spreadsheet where I want to paste them select the cells where they are to be pasted paste (command v) and the data is entered as a date but they have all changed what was 05/08/2005 in one sheep has become 05/09/2001 in the second sheet! without knowing the reasons of your problem, and in addition what Brian wrote, you could try the following: As dates are represented internally by (integer) numbers, please select the cells containing the dates before you copy them, and set the cell format to standard. Then all dates should turn into numbers. What happens if you copypaste those numbers to your new spreadsheet? Do the numbers change? If not, you can then select the target cells and format them as date - and see if they are correct now. Nino -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted Eugenie (Oogie) McGuire Desert Weyr http://www.desertweyr.com/ Paonia, CO USA -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Paste of Dates Incorrect in Calc
Hi. Take a look at https://help.libreoffice.org/3.6/Calc/Date_and_Time_Functions To me the difference you are getting looks close to the difference between the date bases for day zero. Steve On 2013-05-13 08:45, Oogie McGuire wrote: OK following up on the suggestions. First the user profile page linked to doesn't exist so I can't try that. I upgraded my system to the latest LibreOffice in case that was a problem am running 3.6.6.2 (Build ID: f969faf) All data was originally created on the same machine in the US locale not changed. I have tried all subsets of paste special I can think of. I tried setting the format of the cells to number general. Copied those, pasted into a new spreadsheet, The numbers did not change at all. But when I then set the format to date the dates are not the same and are incorrect. Tried the copy within the sheet where the dates are correct and it works properly Tried a select of the dates where they are correct. get a brand new blank spreadsheet, paste the dates. They are incorrect Tried copy of the dates after they have been changed to format as numbers create a brand new spreadsheet paste verify the numbers are the same change the format to date and then the dates are incorrect. I'd be glad to send the info to anyone else to se if they can figure it out. Not sure how or where to upload a file though On May 12, 2013, at 2:13 PM, Nino Novak wrote: Am 12.05.2013 21:00, schrieb Oogie McGuire: In the first spreadsheet I select the cells containing the dates I want. copy (command c) move to the spreadsheet where I want to paste them select the cells where they are to be pasted paste (command v) and the data is entered as a date but they have all changed what was 05/08/2005 in one sheep has become 05/09/2001 in the second sheet! without knowing the reasons of your problem, and in addition what Brian wrote, you could try the following: As dates are represented internally by (integer) numbers, please select the cells containing the dates before you copy them, and set the cell format to standard. Then all dates should turn into numbers. What happens if you copypaste those numbers to your new spreadsheet? Do the numbers change? If not, you can then select the target cells and format them as date - and see if they are correct now. Nino -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted Eugenie (Oogie) McGuire Desert Weyr http://www.desertweyr.com/ Paonia, CO USA -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: Paste of Dates Incorrect in Calc
Hi :) Sorry about that. I had not thought about the commands being slightly different on a Mac but the link should have worked. It should have been clickable. You are not the first to have had troubles with it though. Are you able to do a google search (or similar) to find LibreOffice Publications. That tends to be a good way to get to our wiki. Then on the wiki in the top right corner is a search box to look-up User Profile. It's not swapping MM/DD/ to DD/MM/ is it because the year number is changing too. Are the dates aligned to the left of the cell (like text) or to the right (like numbers)? I take it you have tried creating 2 new files and keying in a few dates and then copying those to the other new file? You stated all other combinations except for that one and i don't know if that was me just not noticing (again). Apols and regards from Tom :) -- View this message in context: http://nabble.documentfoundation.org/Paste-of-Dates-Incorrect-in-Calc-tp402p4055574.html Sent from the Users mailing list archive at Nabble.com. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Paste of Dates Incorrect in Calc
Hi :) That looks like the winner to me. Ignore my post Regards from Tom :) From: Steve Edmonds steve.edmo...@ptglobal.com To: Oogie McGuire oog...@desertweyr.com Cc: users@global.libreoffice.org Sent: Sunday, 12 May 2013, 22:09 Subject: Re: [libreoffice-users] Re: Paste of Dates Incorrect in Calc Hi. Take a look at https://help.libreoffice.org/3.6/Calc/Date_and_Time_Functions To me the difference you are getting looks close to the difference between the date bases for day zero. Steve On 2013-05-13 08:45, Oogie McGuire wrote: OK following up on the suggestions. First the user profile page linked to doesn't exist so I can't try that. I upgraded my system to the latest LibreOffice in case that was a problem am running 3.6.6.2 (Build ID: f969faf) All data was originally created on the same machine in the US locale not changed. I have tried all subsets of paste special I can think of. I tried setting the format of the cells to number general. Copied those, pasted into a new spreadsheet, The numbers did not change at all. But when I then set the format to date the dates are not the same and are incorrect. Tried the copy within the sheet where the dates are correct and it works properly Tried a select of the dates where they are correct. get a brand new blank spreadsheet, paste the dates. They are incorrect Tried copy of the dates after they have been changed to format as numbers create a brand new spreadsheet paste verify the numbers are the same change the format to date and then the dates are incorrect. I'd be glad to send the info to anyone else to se if they can figure it out. Not sure how or where to upload a file though On May 12, 2013, at 2:13 PM, Nino Novak wrote: Am 12.05.2013 21:00, schrieb Oogie McGuire: In the first spreadsheet I select the cells containing the dates I want. copy (command c) move to the spreadsheet where I want to paste them select the cells where they are to be pasted paste (command v) and the data is entered as a date but they have all changed what was 05/08/2005 in one sheep has become 05/09/2001 in the second sheet! without knowing the reasons of your problem, and in addition what Brian wrote, you could try the following: As dates are represented internally by (integer) numbers, please select the cells containing the dates before you copy them, and set the cell format to standard. Then all dates should turn into numbers. What happens if you copypaste those numbers to your new spreadsheet? Do the numbers change? If not, you can then select the target cells and format them as date - and see if they are correct now. Nino -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted Eugenie (Oogie) McGuire Desert Weyr http://www.desertweyr.com/ Paonia, CO USA -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
