[Solved] Date differential

Discuss the spreadsheet application

[Solved] Date differential

Postby rbc » Fri Feb 15, 2019 4:15 am

I created a spreadsheet 14 years ago (2005). I found similar information online and began to make note of the findings. Consistently, without failure, my dates are four years earlier from those posted. Any explanation? I can understand a typo or two but this is a list of 300 entries!

I'm using Mac OpenOffice 4.1.6
Last edited by rbc on Fri Feb 15, 2019 6:39 pm, edited 1 time in total.
-------------
Mac OSX 10.11.6 El Capitan
Apache OpenOffice 4.1.7 (2018)
rbc
 
Posts: 43
Joined: Mon Jul 28, 2014 5:26 am

Re: date differential

Postby robleyd » Fri Feb 15, 2019 4:45 am

Possibly the software you used then used a different 'base date' than your current settings. As you are on Mac, you'll find your current setting under Date in OpenOffice | Preferences | Calc | Calculate [pretty sure that is where - I'm sure you'll find it]

Help tells us about Date:
Date
Select the start date for the internal conversion from days to numbers.
30/12/1899 (default)
Sets 1899-12-30 as day zero.
01/01/1900 (StarCalc 1.0)
Sets 1900-01-01 as day zero. Use this setting for StarCalc 1.0 spreadsheets containing date entries.
01/01/1904
Sets 1904-01-01 as day zero. Use this setting for spreadsheets that are imported in a foreign format.
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3057
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: date differential

Postby rbc » Fri Feb 15, 2019 4:50 am

I have default 12/30/1899 (default).

Been awhile, but I thought Macs use date as 1/1/1904. Would this account for difference?
-------------
Mac OSX 10.11.6 El Capitan
Apache OpenOffice 4.1.7 (2018)
rbc
 
Posts: 43
Joined: Mon Jul 28, 2014 5:26 am

Re: date differential

Postby RusselB » Fri Feb 15, 2019 5:19 am

This does account for the difference.
As the files were created 14 years ago, is it possible that they were done on a different machine?
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5667
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: date differential

Postby rbc » Fri Feb 15, 2019 6:14 am

RusselB wrote:This does account for the difference.
As the files were created 14 years ago, is it possible that they were done on a different machine?


Yes the Mac before my current iMac, yes. Obviously, older OS too. But still Mac. Should I "accept" that my dates are 4 years out and the other data is correct? I'm okay with that, just want to make sure and know what to set so it can't happen again.
-------------
Mac OSX 10.11.6 El Capitan
Apache OpenOffice 4.1.7 (2018)
rbc
 
Posts: 43
Joined: Mon Jul 28, 2014 5:26 am

Re: Date differential

Postby RoryOF » Fri Feb 15, 2019 10:15 am

In /Tools /Options /OpenOffice-Calc /Calculate you can select a choice of three base dates for your spreadsheet. You just need to reselect the correct one for your dates.

Note that on a Mac, Options in the above path is under Preferences.
Apache OpenOffice 4.1.7 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29871
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Date differential

Postby robleyd » Fri Feb 15, 2019 11:04 am

Yes the Mac before my current iMac, yes. Obviously, older OS too. But still Mac.

But what was the software and what start date was it configured for? As you may know, dates are stored as a number representing 'days since start date'; so if the old software had a different start date to the current software, there will be an anomaly.
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3057
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Date differential

Postby rbc » Fri Feb 15, 2019 1:16 pm

robleyd wrote:
Yes the Mac before my current iMac, yes. Obviously, older OS too. But still Mac.

But what was the software and what start date was it configured for? As you may know, dates are stored as a number representing 'days since start date'; so if the old software had a different start date to the current software, there will be an anomaly.


It was probably Microsoft Office (Excel) as I didn't switch to OpenOffice until 2009. Whatever default they used is how the spreadsheet was created. I don't have access to MSExcel so can't verify. I'll add the four years as accuracy is important for death dates!
-------------
Mac OSX 10.11.6 El Capitan
Apache OpenOffice 4.1.7 (2018)
rbc
 
Posts: 43
Joined: Mon Jul 28, 2014 5:26 am

Re: Date differential

Postby rbc » Fri Feb 15, 2019 1:19 pm

robleyd wrote:
Yes the Mac before my current iMac, yes. Obviously, older OS too. But still Mac.

But what was the software and what start date was it configured for? As you may know, dates are stored as a number representing 'days since start date'; so if the old software had a different start date to the current software, there will be an anomaly.


I should ask: how do I add four years to the date? I have them in DD/MM/YYYY format.
-------------
Mac OSX 10.11.6 El Capitan
Apache OpenOffice 4.1.7 (2018)
rbc
 
Posts: 43
Joined: Mon Jul 28, 2014 5:26 am

Re: Date differential

Postby keme » Fri Feb 15, 2019 2:21 pm

rbc wrote:I should ask: how do I add four years to the date? I have them in DD/MM/YYYY format.

As RoryOf explained above:
  • Menu selection OpenOffice - Options
  • Category (left pane of the dialog) Openoffice Calc - Calculate
  • Select Date: 1/1/1904
You have to do this with your file open. The setting is saved with the file. Note that the setting may also stick, so if you restart Calc "empty", i.e. without opening a file, check that you have the setting you want. If you exchange spreadsheet files with other people, you should use the 1899 reference date mostly.

Note that copy/paste between files using different "epoch" (the "zero day" reference date you set by the procedure above) may give wrong dates pasted. The number stored in the spreadsheet is "count of days since epoch", and formatting translates this to a valid date. If epoch date is changed, the number is still valid, but changes to a different date. I recognize that this can be confusing. The simple strategy is to just accept that "sometimes it is wrong", and adjust the above setting accordingly. (There are 3 options. If you find that your dates are off, try the other options and use the one that makes more sense.)
User avatar
keme
Volunteer
 
Posts: 3284
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Date differential

Postby Villeroy » Fri Feb 15, 2019 3:38 pm

OK, you can adjust the mapping of day numbers in the options dialog to adjust this spreadsheet document. Within this document everything will work as expected.

rbc wrote:I should ask: how do I add four years to the date? I have them in DD/MM/YYYY format.

This is a legitimate question. When you copy day numbers between this document with 1904 setting and other documents with standard setting, the days will be 1462 days off (difference between 1899-12-30 and 1904-01-01). First of all, the formatting of day numbers does not make any difference at all. You may even format them as currency values without losing any information and all calculation working correctly.
Enter number 1462 in a cell and copy that cell.
Select one range of date cells and paste-special (Ctrl+Shift+V).
In the upcoming dialog choose options "Numbers" and "Add" before hitting OK.
This will add the value of the copied cell to all numeric cells within the selection.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27373
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Date differential

Postby rbc » Fri Feb 15, 2019 6:26 pm

rbc wrote:
robleyd wrote:
Yes the Mac before my current iMac, yes. Obviously, older OS too. But still Mac.

But what was the software and what start date was it configured for? As you may know, dates are stored as a number representing 'days since start date'; so if the old software had a different start date to the current software, there will be an anomaly.


I should ask: how do I add four years to the date? I have them in DD/MM/YYYY format.



Sorry, didn't phrase properly. For the dates entered that I now see are off by the 4 years, how do I add "4" to the dates? 15/2/2015 should be 15/2/2019 for example.
-------------
Mac OSX 10.11.6 El Capitan
Apache OpenOffice 4.1.7 (2018)
rbc
 
Posts: 43
Joined: Mon Jul 28, 2014 5:26 am

Re: Date differential

Postby Villeroy » Fri Feb 15, 2019 6:35 pm

add 1462 to all dates as outlined above
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27373
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 16 guests