![]() |
|
The Off-Topic Lounge APPROPRIATE FAMILY-FRIENDLY TOPICS ONLY - READ THE RULES! This forum is for posting anything (excluding topics prohibited by the forum rules) that's unrelated to email. General discussions, in other words. |
![]() |
|
Thread Tools |
![]() |
#1 | |
The "e" in e-mail
Join Date: Feb 2006
Location: EU
Posts: 4,983
|
Excel just entered its 40th year
Quote:
|
|
![]() |
![]() |
![]() |
#2 |
Moderator
Join Date: Nov 2001
Location: British Columbia
Posts: 4,053
|
I remember when Excel for Windows and Word for Windows came with a bare bones version of Windows.
|
![]() |
![]() |
![]() |
#3 |
Moderator
Join Date: Nov 2001
Location: British Columbia
Posts: 4,053
|
Interesting thing about Excel is that it did not take into account that there was no February 29th in the year 1900.
In Excel, day 1 was January 1st 1900. When Microsoft released Access, they synced the dates on March 1st 1900 as day 61. In Access this means day 1 is December 31st 1899. I discovered this on February 29th, 2000. I was running a routine in Access involving the DOS mm-dd-yy timestamp on files. The routine wasn't Y2K compatible, but it didn't matter. Except for that day where my function generated an error message. For me it was serendipity because I had written a database where I used the day numbers and I couldn't understand why day 1 was December 31st 1899. |
![]() |
![]() |
![]() |
#4 |
Intergalactic Postmaster
Join Date: Oct 2002
Location: Holon, Israel.
Posts: 5,002
|
John Walker writes about the MS Excel 29 February 1900 bug and more on dates in Excel here in his Fourmilab's Calendar Converter.
|
![]() |
![]() |
![]() |
#5 |
Essential Contributor
Join Date: Mar 2007
Location: UK
Posts: 283
|
Excel is great, but it would be even greater if they had stuck with and extended the original concept, instead of shoehorning in ugly new features that undermined its logical structure.
For example: Giving the columns letters instead of numbers. (How many cells to the right of column Q is column AH?! Ridiculous.) Introducing "merged cells", which prevent you from selecting a single column or row, instead of extending the principle of "center across columns" so that you could, for example "center down rows" or "right align across columns". Changing the way lines on cell borders were defined so that it became ambiguous. Abandoning the straightforward and elegant Excel 5 Macro language and replacing it with the cumbersome and hard-to-understand "Visual Basic". I could go on. ...And I often do ! Last edited by Grhm : 30 Oct 2024 at 11:51 AM. |
![]() |
![]() |
![]() |
#6 |
Junior Member
Join Date: Nov 2024
Posts: 1
|
It's wild that Excel's date system doesn't account for February 29th in 1900—such a quirky mistake. I had a similar moment when I was working with dates in Excel and Access years ago. I kept trying to split date and time in excel for a project, but the results were all over the place. Took me way too long to realize that the issue was with how Excel handled its dates. A little trick that helped me out was using the TEXT function to separate date and time, something like `=TEXT(A1,"mm/dd/yyyy")` for the date and `=TEXT(A1,"hh:mm:ss")` for the time. It really cleaned things up! Anyone else have those “aha!”
Last edited by KerryJohnson : 27 Dec 2024 at 08:26 PM. |
![]() |
![]() |
![]() |
#7 |
Moderator
Join Date: Nov 2001
Location: British Columbia
Posts: 4,053
|
Generally, I like to use the "yyyy-mm-dd" format for dates. I will also use "ddd yyyy-mmm-dd".
I usually only use the TEXT function when I am building a string with the date in it. e.g. Code:
="The deadline is "&TEXT(U12,"dddd mmmm d")&INDEX(day_suffix!D5:D35,DAY(U12))&"." If U12 was 2025-02-22, then the string would be ... ... "The deadline is Saturday February 22nd." As far as I know the TEXT function does not have an option for the day suffix, so I had to cobble my own method. |
![]() |
![]() |
![]() |
#8 | |
Moderator
Join Date: Nov 2001
Location: British Columbia
Posts: 4,053
|
Quote:
When you say "how Excel handled its dates", are you talking about how Excel stores the time as a decimal part of a day? I'm not sure why you want to separate date and time, but you could just separate the decimal part. eg. A1 contains 45634.6329167If you made custom formats for the cells .. A1 ... mm/dd/yyyy hh:mm:ssThe the cells would display ... A1 ... 12/08/2024 15:11:24Incidentally, I mentioned in my preceding post that I prefer putting the year first. The reason is in Canada some institutions use mm/dd/yy and others use dd/mm/yy, like I was taught in school. So the example date could be interpreted as either December 8th or August 12th. With the year first with 4 digits, there is no confusion. If you store the date as text, it would be difficult to convert. If you store the date as a number, you just have to change the format. And, as I discovered recently, you can search and replace custom formats. |
|
![]() |
![]() |
![]() |
#9 |
Cornerstone of the Community
Join Date: Apr 2005
Location: NYC
Posts: 534
|
Do people use it at home, or just at work?
I'm thinking of trying it at home. |
![]() |
![]() |
![]() |
#10 | |
Moderator
Join Date: Nov 2001
Location: British Columbia
Posts: 4,053
|
Quote:
|
|
![]() |
![]() |
![]() |
#11 |
Intergalactic Postmaster
Join Date: Oct 2002
Location: Holon, Israel.
Posts: 5,002
|
I don't use Excel at home because I don't have MS Office installed on my PC, but I do use spreadsheets not just for work. I have Calc on my PC (as part of LibreOffice) but I mostly use online spreadsheets at Google Spreadsheets.
I have one spreadsheet I use for recording my blood pressure (and a Google form to add data to that spreadsheet). I also keep an inventory of the pills I take on that spreadsheet. I have a spreadsheet where once every month I record usage of some utilities (electricity and water) and that allows me to graph average daily consumption. I found out now that I have a spreadsheet recording birthdays in my family, that I stopped updating around 2006 ... Sometimes I use a spreadsheet instead of a calculator. |
![]() |
![]() |
![]() |
Thread Tools | |
|
|