[Solved] Date differential

Discuss the spreadsheet application
Post Reply
rbc
Posts: 51
Joined: Mon Jul 28, 2014 5:26 am

[Solved] Date differential

Post by rbc »

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.1574 Catalina
According to topic 104522 rbc is switching from OpenOffice to LibreOffice
User avatar
robleyd
Moderator
Posts: 5056
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: date differential

Post by robleyd »

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
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
rbc
Posts: 51
Joined: Mon Jul 28, 2014 5:26 am

Re: date differential

Post by rbc »

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.1574 Catalina
According to topic 104522 rbc is switching from OpenOffice to LibreOffice
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: date differential

Post by RusselB »

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, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
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.
rbc
Posts: 51
Joined: Mon Jul 28, 2014 5:26 am

Re: date differential

Post by rbc »

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.1574 Catalina
According to topic 104522 rbc is switching from OpenOffice to LibreOffice
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Date differential

Post by RoryOF »

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.15 on Xubuntu 22.04.4 LTS
User avatar
robleyd
Moderator
Posts: 5056
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Date differential

Post by robleyd »

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
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
rbc
Posts: 51
Joined: Mon Jul 28, 2014 5:26 am

Re: Date differential

Post by rbc »

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.1574 Catalina
According to topic 104522 rbc is switching from OpenOffice to LibreOffice
rbc
Posts: 51
Joined: Mon Jul 28, 2014 5:26 am

Re: Date differential

Post by rbc »

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.1574 Catalina
According to topic 104522 rbc is switching from OpenOffice to LibreOffice
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Date differential

Post by keme »

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
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Date differential

Post by Villeroy »

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
rbc
Posts: 51
Joined: Mon Jul 28, 2014 5:26 am

Re: Date differential

Post by rbc »

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.1574 Catalina
According to topic 104522 rbc is switching from OpenOffice to LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Date differential

Post by Villeroy »

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply