Re: [libreoffice-users] Date problem in LO Calc 4.0.3.3

At 09:02 05/06/2013 -0700, Spencer Graves wrote:
> On 6/4/2013 9:23 PM, Brian Barker wrote:
> > At 16:41 04/06/2013 -0700, Spencer Graves wrote:
> > > When I copy a date from one LO spreadsheet to another, it changes.
> >
> > Much as you might be surprised by this, it is, I think, by design.
> >
> > > Specifically, I have *.ods files DateProblem2add.ods and 
> > > DateProblemMerge.ods.  The first contains only 2013-05-30 in cell 
> > > A1.  The second contains only 2012-12-9 in cell A1.  I copy the 
> > > first date into cell A3 of the second file, where it appears 
> > > 2009-05-29 -- four years and one day before the date I thought I 
> > > was copying -- which is what I see in that cell in the attached 
> > > DateProblemMerge.ods.  This is using LO 4.0.3.3 under Windows 7.
> >
> > Dates and times in a spreadsheet are stored internally as days and 
> > fractions of a day from a selected origin.  Displayed values such 
> > as your "2013-05-30" are merely the result of cell formatting.  If 
> > you go to Tools | Options... | LibreOffice Calc | Calculate | Date, 
> > you will see that there are three date origins to choose from.  If 
> > you copy dates between spreadsheets, the underlying numbers are 
> > copied.  If the date origins are different for your two 
> > spreadsheets, the copied values will be interpreted and displayed 
> > as different dates.
> >
> > You will notice that two of the date origin choices are four years 
> > and two days apart.  Er, that's indeed four years and one day if 
> > you allow for Microsoft Excel's false belief that 1900 was a leap year.
>
> I just copied dates from LO Calc to MS Paint.  Different dates with 
> different date origin settings in LO displayed correctly.

Ah, but that's different: Paint is a graphic application, not a 
spreadsheet, so you are pasting an image of the display in the 
spreadsheet, not the underlying value with which you can continue to 
calculate.  So yes: in that case, you will of course see what you see.

There are various workarounds for copying values in the way you 
require, but you need to do some of the work yourself:

o Paste the values into the destination sheet as a link - using Paste 
Special.  Now copy the values in the new sheet and paste them back 
(possibly even over themselves), but using Paste Special with "Date & 
time" ticked but Formulas not ticked.

o Form text values from your dates in the original spreadsheet, using 
something like =TEXT(Xn;"YYYY-MM-DD").  Copy these values to the new 
spreadsheet, using Paste Special and again pasting values and not 
Formats.  You now have text displays in your preferred format; if you 
need *values*, use =DATEVALUE() to extract these.

o Do you need to calculate with these dates at all in the new 
sheet?  If not, you can leave them as text strings and avoid the 
problem altogether.

o Choose the same date origin for both spreadsheets!

> This suggests to me that it should be possible to modify the code 
> for paste in LO Calc so the result is not garbled when the date 
> origins are different between the target spreadsheet and the one 
> from which the date was copied.

It would, but - as I suggested - I imagine people will not want to 
make such a change.

> I've contributed solutions to date-origin problems crudely like this 
> to the Free Open-Source Software R, www.r-project.org. However, I've 
> done so little with this kind of programming to volunteer to fix it myself.

Before anyone can "fix" it, you need to convince people that the 
change would be desirable, i.e. that they should see it as a bug.  I 
don't think they will.  What would you want to happen, for example, 
if you copied a value formatted as a date but pasted it without this 
formatting into the other sheet.  Would you want the underlying 
number to be modified as if it were a date (which it now isn't) or not?

Brian Barker


--
To unsubscribe e-mail to: users+unsubscribe@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