A potential issue with dates before 1/1/1900 is that more people end up calculating datedif or other formulas that straddle the phantom 29Feb1900
I think it’s time they fix that too. Give people an option to erroneously count the fictitious date (ie January 1, 1900 = 1 and 2/29/1900 exists) or correctly omit it (ie December 31, 1899 = 1 and 2/29/1900 doesn’t exist).
Obviously you’d have to keep March 1, 1900 & later what they are currently and only mess with February 1900 & earlier or it would be a disaster.
Or you could somehow keep every date as it currently is and write override logic for every possible thing you might want to do with two dates if they straddle 2/29/1900 but that’s messy.
But even if they put in pre-1900 dates and left 2/29/1900 as a valid date, that’s still light years better than just ignoring 1899 & earlier altogether.
that’s still light years better than just ignoring 1899 & earlier
2 Likes
RN
Shirley, you saw that coming?
1 Like
DON’T CALL ME SHIRLEY!!!
I don’t have the link but a while back read an extensive analysis on why this is difficult. Whatever product predated Excel 1.0 functioned the same way for dates, Excel inherited it, and apparently it’s deeply ingrained in the source code in a way that has deep implications across the code.
Are you talking about 2/29/1900 or dates before 1900?
My recollection is that Excel 1 correctly recognized that 2/29/1900 is not a date but the vastly more popular Lotus 123 had it as a valid date. Both softwares programmed 1/1/1900 as 1, and every subsequent day was 1 higher. So like 1/31/1900 was 31 and 2/28/1900 was 59.
But in Lotus 123 60 was 2/29/1900 and in Excel 60 was 3/1/1900. Thus every day after 2/28/1900 was off between the two softwares.
To make the softwares compatible in order to try to gain market share by allowing customers to convert their Lotus 123 files to Excel, Microsoft deliberately “broke” Excel to have it recognize 2/29/1900 as a valid date even though they knew it wasn’t.
Other software has a later start date as 1 and allows negative numbers so you can at least go as far back as the switch from the Julian to the Gregorian calendar accurately.
tl;dr Microsoft knows it’s an issue, but they choose not to fix it for backwards compatibility reasons. Also, computers didn’t have a lot of memory in the old days.
1 Like
Eh, they could fix it by making 12/31/1899 = 1 and then every date from 3/1/1900 and forward wouldn’t change… only dates between 1/1/1900 & 2/28/1900 (which is only 59 days) would change. You’d never want to re-number 3/1/1900 & forward… that would be a disaster.
If they did that and allowed negative numbers to be valid dates at the same time (at least going back to the West adopting the Gregorian calendar) that would be nice.
That’s the explanation I remember reading. I thought it was something about the actual code being carried over from Lotus to Excel, but looks like it’s just for importing old sheets.
It was to make it easier to migrate to Excel from Lotus. Excel had many advantages over Lotus at the time. Lotus had only inertia of companies’ management.
My recollection was this. Lotus 1-2-3 was the dominant spreadsheet of the 1980’s and early 1990s, but when Windows became the dominant operating system, then 1-2-3 was chronically and suspiciously unstable under the Microsoft O/S. And, Oh Gee, Excel was stable. Go figure (pun absolutely intended)
One setting it would be nice for excel to have is that pivot tables always get set up in tabular form, without subtotals or grandtotals, and with item labels repeating. AKA, like SQL, without setting up SQL
2 Likes
Stop fucking using pivot tables for analysis.
3 Likes
Can you explain? Should excel not be used for analysis? Should pivot tables not be used? If pivots can be used, how can they be used? What do you mean by analysis?
1 Like
For better or worse, in many of our offices Excel is the lowest common denominator.
For many tasks, there are better tools. But Excel is the one that mostly everyone knows, and that IT has no qualms about installing.
Yesterday, I had a discussion that included the question, “why are you using Excel for [this]”?
My response “Well [Person 1] built something in R. They left and were replaced by [Person 2] who built something in SAS. Then they left and [Person 3] wanted to use Python…but I got tired of having the work rebuilt every time we had a new team member because they weren’t skilled in the tool the prior person had expertise in. So it’s in Excel.”
EDIT: I’ll add that my two most common uses of pivot tables are:
-
My company has pivot-table like cubes as a front end to our premium & loss data warehouse. For rigorous analysis, querying the underlying database in SQL is preferable, but when you’re looking for a quick and dirty “premium and losses for (products/lines X, Y, and Z) by state, by year” or “reported loss triangles for (products/lines A, B, and C)”, it’s faster/easier to just play with the pivots.
-
If I have a table in the form of [Field_1], [Field_2], [Data] and I want a list of the various combinations of [Filed_1] and [Field_2], a pivot table makes it trivial. Killing subtotals and asking for labels to repeat every time I need to do the task is annoying.
5 Likes
You could UNIQUE() and get to the same thing.
or Remove Duplicates.
Yeah, I was thinking of adding a thread for sharing uses of all new Excel functions that are array based.
I think the new array based Excel functions could be a pivot table killer. I mean, it’s nice to have pivot tables available but to answer 1 question from a dataset, array functions might be easier than the time involved with setting up and perfecting a pivot table.
1 Like