MS Excel questions

Post Reply
User avatar
Cakedaddy
Posts: 8798
Joined: Thu May 20, 2004 6:52 pm

MS Excel questions

Post by Cakedaddy »

I have used Openoffice for years and years. Recently switched to MS Office for 'better' compatibility with others.

In Openoffice, if I don't change the cell formatting, leaving it as default (General), when I entered a date, it would be formatted as: mm/dd/yy (5/12/21)

In Excel,when entering a date, it would show: month-day (May-5)

How do I change Excel's default (General) so that it's mm/dd/yy? I know I can highlight the cell and change it's format. But this particular column has text, dates, and numbers in it. So constantly changing formatting is tedious. I got used to Openoffice's defaults, and want Excel to match them.

I did change the regional settings in Control Panel, but did not affect Excel. (one googled suggestion)
TheCatt
Site Admin
Posts: 53728
Joined: Thu May 20, 2004 11:15 pm
Location: Cary, NC

MS Excel questions

Post by TheCatt »

I went to a new spreadsheet, and typed in the following:
4/5/2021
tete
203
20
ffffff
4/1/2021
4/1/21
5/4

Excel did all the dates as M/D/YYYY, except when I only typed in 5/4... which is still interpreted as 5/4/2021, but displayed as 4-May. Just add the year?
It's not me, it's someone else.
User avatar
Cakedaddy
Posts: 8798
Joined: Thu May 20, 2004 6:52 pm

MS Excel questions

Post by Cakedaddy »

All that eeextrrrraaaa tyyyypinnnnnnnggggg.

Quickbooks works/looks the same as open office. You type 5/14, and it assumes you mean 05/14/2021 and enters that. I prefer 5/14/21 (you know. To save drive space), but don't mind the leading 0 and 20. I just appreciate that OO and QB assumes this year and keeps the date format I started with and don't go all Apple on me and say "This is what you will use because we think it looks better".
TheCatt
Site Admin
Posts: 53728
Joined: Thu May 20, 2004 11:15 pm
Location: Cary, NC

MS Excel questions

Post by TheCatt »

I assume this is the google you found
When you enter some text into a cell such as "2/2", Excel assumes that this is a date and formats it according to the default date setting in Control Panel. Excel might format it as "2-Feb". If you change your date setting in Control Panel, the default date format in Excel will change accordingly. If you don’t like the default date format, you can choose another date format in Excel, such as "February 2, 2012" or "2/2/12". You can also create your own custom format in Excel desktop.
My default is already M/D/YYYY, so Excel is just ignore that for some reason
It's not me, it's someone else.
TheCatt
Site Admin
Posts: 53728
Joined: Thu May 20, 2004 11:15 pm
Location: Cary, NC

MS Excel questions

Post by TheCatt »

Ctrl-Shift-# (ctrl-shift-3) on the cell will change the format as well, without extra clicking, but yes, some typing.
It's not me, it's someone else.
User avatar
Cakedaddy
Posts: 8798
Joined: Thu May 20, 2004 6:52 pm

MS Excel questions

Post by Cakedaddy »

Mine was not that one, but similar. I to have my date set to m/d/yy in Control Panel, Regional, etc. But Excel is ignoring it.

Another annoyance. If I enter a date in a 'General' formatted cell, it changes the format of the cell to 'Custom' and now, any time I enter anything in that cell, it assumes I want it formatted as such.

For example, I enter 5/14 and it converts to 14-May. I then decide I want the number 235 in that cell, but it converts it to 22-Aug (8/22/1900). Holy fuck is Excel annoying. Openoffice, so far, is WAY better at doing what I meant, and not what I said. Excel assumes completely wrong, alot.
User avatar
Cakedaddy
Posts: 8798
Joined: Thu May 20, 2004 6:52 pm

MS Excel questions

Post by Cakedaddy »

Ctrl+shift+# converts 14-May to 14-May-21 for me.
User avatar
Cakedaddy
Posts: 8798
Joined: Thu May 20, 2004 6:52 pm

MS Excel questions

Post by Cakedaddy »

I've been looking at dates the same way since, I don't know, when I started caring about dates? 1980 something? I guess I have to change now because MS is stupid. Do accountants use 14-May-21?
Last edited by Cakedaddy on Wed May 12, 2021 7:39 pm, edited 1 time in total.
TheCatt
Site Admin
Posts: 53728
Joined: Thu May 20, 2004 11:15 pm
Location: Cary, NC

MS Excel questions

Post by TheCatt »

Cakedaddy wrote: Wed May 12, 2021 12:46 pm I've been looking at dates the same way since, I don't know, what I started caring about dates? 1980 something? I guess I have to change now because MS is stupid. Do accountants use 14-May-21?
Europeans do, I think.

Honestly, as this point in life, I hate all date formats.
It's not me, it's someone else.
Post Reply