But without MS Excel in the chain, this is already broken in LO. Using the .ods formular, which is working, saving as .xlsx, still working. But after reopening the .xlsx file, the formular is broken.
To generate an address string for INDIRECT() to parse, it is usually wise to use the ADDRESS() function. This function is available and used the same way in all relevant spreadsheet applications, and will insert the correct separators for the application.
Try =INDIRECT(ADDRESS(4;5;1;1;$A$3-1))
If you need to follow the existing E4 cell (in case of moving the data, as may happen e.g. with column/row insertion):
=INDIRECT(ADDRESS(ROW($E$4);COLUMN($E$4);1;1;$A$3-1))
Actually it can, sort of, but there are pitfalls. The limitation is that Excel cannot work in date/time context with a number which - based on the chosen “epoch” (time reference point) - would end up before 1.1. 1900. If you use the “1904 base”, you can work with up to 4 years worth of negative time.
I do not recommend the “base 1904” strategy, but I use it myself 
If you venture down this path, make sure you know what you are doing, noting the following pitfalls:
- Keyed-in entry of negative time values in Excel will still be a challenge.
- if your file interacts (including copy/paste interaction) with other spreadsheets using “standard time”, exchanged dates will most likely be off by 1461 - 1463 days.
- When you change epoch in an existing file, the underlying numbers will usually remain the same, so date interpretation will change.
Disclaimer
I intentionally left out a “howto” description for “1904 epoch” from my suggested solution. I repeat, do not use it unless you really need this with Excel compatibility, you know what you are doing and have made sufficiently sure that it will be safe for your purposes.
In Excel
- Click File in the top menu, and select Options at the bottom (all the way down, may be detached from other entries in the menu)
- Select the Advanced entry in the left pane of the dialog
- Use keyboard shortcut Alt-9 or scroll down near the bottom to find the “Use 1904” entry. Tick!
In Calc
- Menu option Tools - Options
- Left pane, select branch Calc - Calculate
- Top right, select 01.01.1904
We already had issues with wrong interpretation when people switched to base 1904. I wondered that these old legacy burdon still exists in today software.
Just for a quick understanding, when using the base-1904 approach then global policy for all team members would require all to switch, if not differently configured Excel would interpret different dates from the file data? Hard to believe, that would mean every external contact would need the hint “Please reconfigure your Excel to read this file”, true?
If the Excel default is 1900 (if 1904 option is disabled) and looking at the LO Calc default in the Options->Calc->Calculate->Date is 12/30/1899 and not 01/01/1900 or 01/01/1904, how can that already work when interchanging files with Excel?
I found similar advises to stay with the 1900 default and keep positive and negative values in separate fields:
https://www.pctipp.ch/praxis/office/excel-soll-man-die-1904-datumswerte-nutzen-1906808.html
Which is hard to read for people not involved in this stupid issue.
Yes. If the file in question could have time data linked from/to other spreadsheets, using different epoch would be a recipe for trouble. In that case you would probably need to have a “1904 always” policy in effect.
One other workaround for working with negative time would need a separate “time magnitude” display cell, and a “time sign” display cell, apart from the cells used for calculated time. (You could also have the two concatenated after extraction with ABS()/SIGN()/TEXT(), A mess, either way.) This introduces a new challenge, when you need to link/copy from/to the calculated cells (which are presumably hidden), and not to the displayed data.
Silly stupid, I agree. Nevertheless, I am quite sure that I have found the “1904 suggestion” on Microsoft’s own support pages as the “solution” for managing negative time values, as Ms. Salvisberg also hints towards in her essay which you linked to (thanks for that!).
You say, issues appear when having data linked from other spreadsheets. But what about working with 2 Excel setups on 1 file…?
But as I understand now, the setting is a per-file setting, and the file remembers the configured base? So the 1900 or 1904 setting is stored in the file and not a global configuration? That would make things easier when sharing files with other people which have other settings in their Excel.
If this is true, then I would say ok, for having negative values it’s the work-around to use.
1 Like
Calc uses 1899-12-30 as day zero in order to be compatible with most Excel dates.
Due to Excel’s leap year bug, Excel dates between 1900-01-01 and 1900-02-28 are invalid anyway. Starting at 1900-03-01, both spreadsheets use the same day numbers for the same dates.
Again, just to be clear, is the setting base-1900 or base-1904 stored inside a file OR do I need to adapt the global setting in Excel in order to read files correctly?
The setting is stored in the file, but in case you set to other than »1899-12-30« I would strongly recommend to reset the epoch to 1899-12-30 after saving and closing individual files with different settings.
I don’t get yet, why changing the base to 4 years later makes it possible to display negative values. It just means that value 0 is first day in 1900 or 1904. The real data, is that always an unsigned integer?
And why doesn’t LO Calc have this problem, if the base approach and configuration option exists there as well?
Technically why: it works because Microsofts chosen limitation for time data is not “non-negative”, but rather “not any time before the year 1900”.
Strategically why (they chose this limitation and insist that they stick with it): Anybody’s guess (or you could ask Microsoft).
The original purpose of the “1904 option” was not to accomodate negative time. Rather, it was to add compatibility with the early days’ spreadsheets created on the MacOS platform, where the developers may have chosen 1904 to circumvent the “1900 leap year issue”, or for some other reason which we don’t know. The Excel/negative time case is probably just a (fortunate) side effect.
LO does not have this problem because the issue has been considered explicitly, not as an afterthought.
AFAIR that was Lotus 1-2-3 null date and Microsoft was eager to kill Lotus 1-2-3 (successfully) with Excel so they implemented every nonsense it did (including the 1900 leap year bug) and stuck with it forever. Why they never implemented negative date+time serial numbers I don’t know, at least the OOXML formats would had allowed (old binary BIFF .xls didn’t); probably it can’t be mapped to their internal structures (which BIFF more or less is a dump of) without rewriting everything in that area, just a guess.
1 Like
Something else, after storing as .xlsx …
Hours-Testproject_02.4.xlsx (45.8 KB)
… the font colors are moved from black and grey to blue and green. For instance the green font comes from Style “Calculated”, editing this style I see the font color is still “Dark Gray 1”, but shown as green. Changing the font color of the style does not have any effect. Why? Bug? Excel limitation?
Interestingly Excel does it correctly and shows black and grey font colors.
You have accidentally enabled “value highlighting”.
Press ctrl+F8 to toggle this setting, or select it from the View menu.
1 Like
Can you please not use the Suggest a solution or Answer button for comments or even new questions that should get a new topic instead? Thank you.
1 Like
This is not a solution to your initial problem. Please delete it and repost as a comment.
Because I’ve not seen a global comment button, only the comment on single messages.
Ok, I understand, sorry, I’ll delete and re-post as comment to the initial message, eventhough there already was a related comment from keme1, please also move that…
This is not a correct use, too. New questions need be separate. This overloaded topic will be unmanageable, impossible to understand what its current scope is, will be impossible to answer reasonably, and will help no one else.
Lot of small issues about the same file when migrating to .xlsx …
Ok, next time I will try to keep every single issue as new topic.