[Solved] Date differential
[Solved] Date differential
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
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
Mac OSX 10.1574 Catalina
According to topic 104522 rbc is switching from OpenOffice to LibreOffice
Re: date differential
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.
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.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: date differential
I have default 12/30/1899 (default).
Been awhile, but I thought Macs use date as 1/1/1904. Would this account for difference?
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
Mac OSX 10.1574 Catalina
According to topic 104522 rbc is switching from OpenOffice to LibreOffice
Re: date differential
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?
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.
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.
Re: date differential
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.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?
-------------
Mac OSX 10.1574 Catalina
According to topic 104522 rbc is switching from OpenOffice to LibreOffice
Mac OSX 10.1574 Catalina
According to topic 104522 rbc is switching from OpenOffice to LibreOffice
Re: Date differential
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.
Note that on a Mac, Options in the above path is under Preferences.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Re: Date differential
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.Yes the Mac before my current iMac, yes. Obviously, older OS too. But still Mac.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
Re: Date differential
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!robleyd wrote: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.Yes the Mac before my current iMac, yes. Obviously, older OS too. But still Mac.
-------------
Mac OSX 10.1574 Catalina
According to topic 104522 rbc is switching from OpenOffice to LibreOffice
Mac OSX 10.1574 Catalina
According to topic 104522 rbc is switching from OpenOffice to LibreOffice
Re: Date differential
I should ask: how do I add four years to the date? I have them in DD/MM/YYYY format.robleyd wrote: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.Yes the Mac before my current iMac, yes. Obviously, older OS too. But still Mac.
-------------
Mac OSX 10.1574 Catalina
According to topic 104522 rbc is switching from OpenOffice to LibreOffice
Mac OSX 10.1574 Catalina
According to topic 104522 rbc is switching from OpenOffice to LibreOffice
Re: Date differential
As RoryOf explained above:rbc wrote:I should ask: how do I add four years to the date? I have them in DD/MM/YYYY format.
- Menu selection OpenOffice - Options
- Category (left pane of the dialog) Openoffice Calc - Calculate
- Select Date: 1/1/1904
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.)
Re: Date differential
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.
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.
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.rbc wrote:I should ask: how do I add four years to the date? I have them in DD/MM/YYYY format.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Date differential
rbc wrote:I should ask: how do I add four years to the date? I have them in DD/MM/YYYY format.robleyd wrote: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.Yes the Mac before my current iMac, yes. Obviously, older OS too. But still Mac.
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
Mac OSX 10.1574 Catalina
According to topic 104522 rbc is switching from OpenOffice to LibreOffice
Re: Date differential
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice