[Solved] Date differential

[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

[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.

[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?

[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?

[rbc]

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.

[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.

[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.

[rbc]

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!

[rbc]

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.

[keme]

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.)

[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.

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.

[rbc]

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.

[Villeroy]

add 1462 to all dates as outlined above