Solved Can no longer format date as dd/mm/yyyy in Excel (Office 365)

robgr

Member
Local time
5:21 AM
Posts
52
I have just noticed that appear to no longer be able to format the date as dd/mm/yyyy in Excel (Office 365)
A previous document that had a date cell as =TODAY() and which displayed the date as dd/mm/yyyy is now displaying as 20,11,2021 (current format setting)
However, there is no format type for dd/mm/yyyy when I attempt to reformat the cell back to dd/mm/yyyy
This is the same for any empty cell that I attempt to enter a date for and for new worksheets too
This may have changed with 22504.1000
What am I missing?
 

My Computer

System One

  • Operating System
    Windows 11
    Computer type
    PC/Desktop
    Manufacturer/Model
    PCSpecialist
    CPU
    Intel(R) Core(TM) i7-1070
    Motherboard
    TUF GAMING B460-PLUS
    Memory
    32 GBytes DDR4-2667 (1333 MHz)
    Graphics Card(s)
    NVIDIA GeForce GT 1030
    Sound Card
    ONBOARD 6 CHANNEL (5.1) HIGH DEF AUDIO (AS STANDARD)
    Monitor(s) Displays
    Samsung U32J590UQU
    Screen Resolution
    4k
    Hard Drives
    SYSTEM Win 10 237.22GB SSD Disk 0 238.47GB M2 (mobo)
    DATA Data 931.41GB HD 2.5" Disk 1 931.51GB SATA3
    BACKUPS Backups 2TB HD 3.5" Disk 2 2TB SATA3
    PSU
    CORSAIR 550W CV SERIES™ CV-550 POWER SUPPLY
    Case
    PCS 3601 CASE
    Cooling
    CoolerMaster Hyper 212 (120mm) Fan CPU Cooler Black Edition
    Internet Speed
    57/18 Mbps
    Browser
    Vivaldi
    Antivirus
    Kaspersky Total Security

rpetruzz

Member
Local time
12:21 AM
Posts
2
I just tried to figure this out myself...... It appears that you now need to change the "Locale" in the format cells dialog box.
I was able to change to English (United Kingdom) and it offered me a whole different set of formatting options to use to format the cell.

1637411352861.png1637411426262.png
 

My Computers

System One System Two

  • Operating System
    Windows 10 Pro X64 21H1
    Computer type
    PC/Desktop
    Manufacturer/Model
    DIY Build
    CPU
    AMD Ryzen 7 3800X
    Motherboard
    ASUS ROG STRIX X570-E GAMING
    Memory
    Corsair DDR4-2132 4x16gb Dual Channel
    Graphics Card(s)
    NVIDIA GeForce GTX 1070
    Sound Card
    On motherboard
    Monitor(s) Displays
    2 - NEC PA241W
    Screen Resolution
    1920X1200
    Hard Drives
    2 - Corsair Force MP600 M.2 2280 1TB PCI-Express Gen 4.0 x4 NVMe
    PSU
    850 Watt Corsair® AX850 80+
    Case
    Maingear ATX
    Cooling
    Wraith Prism cooler
    Keyboard
    Microsoft Sidewinder
    Mouse
    Logitech
    Internet Speed
    Spectrum - 200mbs
    Browser
    Chrome
    Antivirus
    Windows Defender
  • Operating System
    Windows 10 Pro X64 21H1
    Computer type
    Laptop
    Manufacturer/Model
    Maingear Element
    CPU
    Intel Core i7 9750H
    Motherboard
    Intel LAPQC71B
    Memory
    32gb - Kingston DDR4 1333 Mhz Dual channel
    Graphics card(s)
    NVIDIA GeForce RTX 2070 Max Q
    Sound Card
    On board
    Monitor(s) Displays
    LCD
    Screen Resolution
    1920x1080
    Hard Drives
    2tb INTEL SSD PEKNW020T8
    PSU
    On board
    Case
    Laptop
    Cooling
    Fans
    Mouse
    TeckNet
    Keyboard
    On Board
    Internet Speed
    Spectrum 200mbps
    Browser
    Chrome
    Antivirus
    Windows Defender

jvickers

Well-known member
Power User
Local time
12:21 AM
Posts
1,622
Location
S-W Georgia
I just tried to figure this out myself...... It appears that you now need to change the "Locale" in the format cells dialog box.
I was able to change to English (United Kingdom) and it offered me a whole different set of formatting options to use to format the cell.

View attachment 14150View attachment 14151
Well, that does make a little sense since dates are typically formatted month/day/year in the U.S. Glad you kind of solved the problem! Day-month-year (like 20-Nov-2021) without switching the locale.
 

My Computers

System One System Two

  • Operating System
    Windows 11 Pro (22000.184)
    Computer type
    PC/Desktop
    Manufacturer/Model
    Home built
    CPU
    Ryzen 7 3700X
    Motherboard
    MSI MPG X570 GAMING EDGE WIFI
    Memory
    Crucial Ballistix Sport LT 3200 MHz DDR4 DRAM Desktop Gaming Memory 32MB
    Graphics Card(s)
    ZOTAC Gaming GeForce GTX 1660 Super 6GB GDDR6 192-bit Gaming Graphics Card/ ZOTAC GeForce GTX 1050 Ti Mini, 4GB GDDR5 DisplayPort 128-bit Gaming Graphic Card
    Sound Card
    Proprietary
    Monitor(s) Displays
    ViewSonic XG2530 25"/Benq XL2411P 24"/ Samsung SyncMaster BX2331 23"
    Screen Resolution
    1920x1080 240Hz/144Hz/60Hz (based on monitor setup above)
    Hard Drives
    ADATA XPG SX8200 Pro 1TB
    ADATA XPG SX8200 Pro 512GB
    Samsung SSD 860 EVO 1TB 2.5 Inch SATA III Internal SSD
    PSU
    Thermaltake Smart 700W 80+ White Certified PSU
    Case
    Rosewill ATX Mid Tower Gaming Computer Case, Gaming Case with Blue LED for Desktop
    Cooling
    Stock Ryzen 7 3700X Fan (Soon to be iCUE H60i RGB PRO XT Liquid CPU Cooler)
    Keyboard
    Corsair K70
    Mouse
    Logitech MX Master 3
    Internet Speed
    120MB/s Download/ 10MB/s Upload
    Browser
    Edge (Chromium)
    Antivirus
    Norton 360
  • Operating System
    Windows 10
    Computer type
    PC/Desktop
    Manufacturer/Model
    Home Built
    CPU
    AMD FX Black Edition
    Motherboard
    Asus M5A97 LE R2.0
    Memory
    16MB DDR
    Graphics card(s)
    I forget, but it's old.
    Sound Card
    Propietary
    Monitor(s) Displays
    ACER LED 24"
    Screen Resolution
    1920X1080
    Hard Drives
    1TB Samsung SSD 3.5"
    Case
    Corsair
    Cooling
    Stock
    Mouse
    Logitech
    Keyboard
    Logitech
    Internet Speed
    120MB/10MB
    Browser
    Chrome
    Antivirus
    Norton 360
    Other Info
    Currently taken down because of a lack of space :-(
    Maybe I'll make room somewhere and connect to the Dev channel if possible.

robgr

Member
Thread Starter
Local time
5:21 AM
Posts
52
I just tried to figure this out myself...... It appears that you now need to change the "Locale" in the format cells dialog box.
I was able to change to English (United Kingdom) and it offered me a whole different set of formatting options to use to format the cell.

View attachment 14150View attachment 14151
I am already on English (United Kingdom) locale. This is what I see
 

Attachments

  • excel format issue.jpg
    excel format issue.jpg
    51.1 KB · Views: 1

My Computer

System One

  • Operating System
    Windows 11
    Computer type
    PC/Desktop
    Manufacturer/Model
    PCSpecialist
    CPU
    Intel(R) Core(TM) i7-1070
    Motherboard
    TUF GAMING B460-PLUS
    Memory
    32 GBytes DDR4-2667 (1333 MHz)
    Graphics Card(s)
    NVIDIA GeForce GT 1030
    Sound Card
    ONBOARD 6 CHANNEL (5.1) HIGH DEF AUDIO (AS STANDARD)
    Monitor(s) Displays
    Samsung U32J590UQU
    Screen Resolution
    4k
    Hard Drives
    SYSTEM Win 10 237.22GB SSD Disk 0 238.47GB M2 (mobo)
    DATA Data 931.41GB HD 2.5" Disk 1 931.51GB SATA3
    BACKUPS Backups 2TB HD 3.5" Disk 2 2TB SATA3
    PSU
    CORSAIR 550W CV SERIES™ CV-550 POWER SUPPLY
    Case
    PCS 3601 CASE
    Cooling
    CoolerMaster Hyper 212 (120mm) Fan CPU Cooler Black Edition
    Internet Speed
    57/18 Mbps
    Browser
    Vivaldi
    Antivirus
    Kaspersky Total Security

Ghot

Well-known member
Power User
VIP
Local time
12:21 AM
Posts
1,530
Location
PA, USA
Month only goes up to 12
Day can go to 31
Year can go to infinity.

Which is why it should be mm/dd/yyyy
 

My Computers

System One System Two

  • Operating System
    Win 10 Home 10.0.22000.348 (x64)
    Computer type
    PC/Desktop
    Manufacturer/Model
    Built by Ghot® [May 2020]
    CPU
    AMD Ryzen 7 3700X
    Motherboard
    Asus Pro WS X570-ACE
    Memory
    G.Skill (F4-3200C14D-16GTZKW)
    Graphics Card(s)
    EVGA RTX 2070 (08G-P4-2171-KR)
    Sound Card
    (onboard) Realtek ALC1220P / ALC S1220A
    Monitor(s) Displays
    Dell U3011 30"
    Screen Resolution
    2560 x 1600
    Hard Drives
    2x Samsung 860 EVO 500GB,
    WD 4TB Black FZBX - SATA III,
    WD 8TB Black FZBX - SATA III,
    LG GH22LS30 CD/DVD Burner
    PSU
    PC Power & Cooling 750W Quad EPS12V
    Case
    COOLER MASTER ATCS 840 Full Tower
    Cooling
    CM Hyper 212 EVO (push/pull)
    Keyboard
    Ducky DK9008 Shine II Blue LED
    Mouse
    Logitech Optical M-100
    Internet Speed
    100/100
    Browser
    Firefox (latest)
    Antivirus
    Bitdefender Internet Security 2020
    Other Info
    Speakers: Klipsch Pro Media 2.1
  • Operating System
    Windows XP Pro 32bit w/SP3
    Computer type
    PC/Desktop
    Manufacturer/Model
    Built by Ghot®
    CPU
    AMD Athlon 64 X2 5000+ (OC'd @ 3.2Ghz)
    Motherboard
    ASUS M2N32-SLI Deluxe Wireless Edition
    Memory
    TWIN2X2048-6400C4DHX (2 x 1GB, DDR2 800)
    Graphics card(s)
    EVGA 256-P2-N758-TR GeForce 8600GT SSC
    Sound Card
    Onboard
    Monitor(s) Displays
    ViewSonic G90FB Black 19" Professional (CRT)
    Screen Resolution
    up to 2048 x 1536
    Hard Drives
    WD 36GB 10,000rpm Raptor SATA
    Seagate 80GB 7200rpm SATA
    Lite-On LTR-52246S CD/RW
    Lite-On LH-18A1P CD/DVD Burner
    PSU
    PC Power & Cooling Silencer 750 Quad EPS12V
    Case
    Generic Beige case, 80mm fans
    Cooling
    ZALMAN 9500A 92mm CPU Cooler
    Mouse
    Logitech Optical M-BT96a
    Keyboard
    Logitech Classic Keybooard 200
    Internet Speed
    100/100
    Browser
    Firefox 3.x ??
    Antivirus
    Symantec (Norton)
    Other Info
    Still assembled, still runs. Haven't turned it on for 13 years?

    http://www.xtremesystems.org/forums/showthread.php?183088-5000-B-E-on-M2N32-SLI-Dlx-Overclocked&p=2891724#post2891724

robgr

Member
Thread Starter
Local time
5:21 AM
Posts
52
Month only goes up to 12
Day can go to 31
Year can go to infinity.

Which is why it should be mm/dd/yyyy
Understood but I don't see any format types using a forward slash as a separator?
 

My Computer

System One

  • Operating System
    Windows 11
    Computer type
    PC/Desktop
    Manufacturer/Model
    PCSpecialist
    CPU
    Intel(R) Core(TM) i7-1070
    Motherboard
    TUF GAMING B460-PLUS
    Memory
    32 GBytes DDR4-2667 (1333 MHz)
    Graphics Card(s)
    NVIDIA GeForce GT 1030
    Sound Card
    ONBOARD 6 CHANNEL (5.1) HIGH DEF AUDIO (AS STANDARD)
    Monitor(s) Displays
    Samsung U32J590UQU
    Screen Resolution
    4k
    Hard Drives
    SYSTEM Win 10 237.22GB SSD Disk 0 238.47GB M2 (mobo)
    DATA Data 931.41GB HD 2.5" Disk 1 931.51GB SATA3
    BACKUPS Backups 2TB HD 3.5" Disk 2 2TB SATA3
    PSU
    CORSAIR 550W CV SERIES™ CV-550 POWER SUPPLY
    Case
    PCS 3601 CASE
    Cooling
    CoolerMaster Hyper 212 (120mm) Fan CPU Cooler Black Edition
    Internet Speed
    57/18 Mbps
    Browser
    Vivaldi
    Antivirus
    Kaspersky Total Security

Bree

Well-known member
Power User
VIP
Local time
5:21 AM
Posts
1,209
Location
S/E England, UK
Month only goes up to 12
Day can go to 31
Year can go to infinity.

Which is why it should be mm/dd/yyyy
For being able to sort by date in text or filenames it is best to use ISO 8601 date format: YYYY-MM-DD
It has the further advantage that it can be extended to include time of day yyy-mm-dd-hh-mm-ss
It can also include the timezone so there can be no confusion about the date.

Even Microsoft use ISO 8601 dates, ever looked at a File History folder?

1637421431617.png
 

My Computers

System One System Two

  • Operating System
    Windows 11 Home
    Computer type
    Laptop
    Manufacturer/Model
    Acer Aspire 3 A315-23
    CPU
    AMD Athlon Silver 3050U
    Memory
    8GB
    Graphics Card(s)
    Radeon Graphics
    Monitor(s) Displays
    laptop screen
    Screen Resolution
    1366x768 native resolution, up to 2560x1440 with Radeon Virtual Super Resolution
    Hard Drives
    1TB HDD
    Browser
    Edge, Firefox
    Antivirus
    Defender
    Other Info
    fully 'Windows 11 ready' laptop. Windows 10 C: partition migrated from my old unsupported 'main machine' then upgraded to 11. A test migration ran Insider builds for 2 months. When 11 was released on 5th October it was re-imaged back to 10 and was offered the upgrade in Windows Update on 20th October.


    My SYSTEM THREE is a Dell Latitude 5410, i7-10610U, 32GB RAM, 512GB ssd, Windows 11 Pro.
  • Operating System
    Windows 11 Pro
    Computer type
    Laptop
    Manufacturer/Model
    Dell Lattitude E4310
    CPU
    i5 M 520
    Motherboard
    0T6M8G
    Memory
    4GB
    Screen Resolution
    1366x768
    Hard Drives
    500GB HDD
    Browser
    Firefox, Edge
    Antivirus
    Defender
    Other Info
    unsupported machine: Legacy bios, MBR, TPM 1.2, upgraded from W10 to W11 using W10/W11 hybrid install media workaround.


    My SYSTEM THREE is a Dell Latitude 5410, i7-10610U, 32GB RAM, 512GB ssd, Windows 11 Pro.

robgr

Member
Thread Starter
Local time
5:21 AM
Posts
52
Just noticed it's the same for Outlook365 but Word365 appears unaffected for an inserted date (in that it appears as Sat, 20/11/2021)
 

Attachments

  • outlook date format.jpg
    outlook date format.jpg
    45.3 KB · Views: 1

My Computer

System One

  • Operating System
    Windows 11
    Computer type
    PC/Desktop
    Manufacturer/Model
    PCSpecialist
    CPU
    Intel(R) Core(TM) i7-1070
    Motherboard
    TUF GAMING B460-PLUS
    Memory
    32 GBytes DDR4-2667 (1333 MHz)
    Graphics Card(s)
    NVIDIA GeForce GT 1030
    Sound Card
    ONBOARD 6 CHANNEL (5.1) HIGH DEF AUDIO (AS STANDARD)
    Monitor(s) Displays
    Samsung U32J590UQU
    Screen Resolution
    4k
    Hard Drives
    SYSTEM Win 10 237.22GB SSD Disk 0 238.47GB M2 (mobo)
    DATA Data 931.41GB HD 2.5" Disk 1 931.51GB SATA3
    BACKUPS Backups 2TB HD 3.5" Disk 2 2TB SATA3
    PSU
    CORSAIR 550W CV SERIES™ CV-550 POWER SUPPLY
    Case
    PCS 3601 CASE
    Cooling
    CoolerMaster Hyper 212 (120mm) Fan CPU Cooler Black Edition
    Internet Speed
    57/18 Mbps
    Browser
    Vivaldi
    Antivirus
    Kaspersky Total Security

robgr

Member
Thread Starter
Local time
5:21 AM
Posts
52
Just realised that it was a recent change to the Region to show the day of the week in the Taskbar Clock that causes this issue
I followed the procedure here but it appears to cause unexpected behaviour for date format elsewhere
 

My Computer

System One

  • Operating System
    Windows 11
    Computer type
    PC/Desktop
    Manufacturer/Model
    PCSpecialist
    CPU
    Intel(R) Core(TM) i7-1070
    Motherboard
    TUF GAMING B460-PLUS
    Memory
    32 GBytes DDR4-2667 (1333 MHz)
    Graphics Card(s)
    NVIDIA GeForce GT 1030
    Sound Card
    ONBOARD 6 CHANNEL (5.1) HIGH DEF AUDIO (AS STANDARD)
    Monitor(s) Displays
    Samsung U32J590UQU
    Screen Resolution
    4k
    Hard Drives
    SYSTEM Win 10 237.22GB SSD Disk 0 238.47GB M2 (mobo)
    DATA Data 931.41GB HD 2.5" Disk 1 931.51GB SATA3
    BACKUPS Backups 2TB HD 3.5" Disk 2 2TB SATA3
    PSU
    CORSAIR 550W CV SERIES™ CV-550 POWER SUPPLY
    Case
    PCS 3601 CASE
    Cooling
    CoolerMaster Hyper 212 (120mm) Fan CPU Cooler Black Edition
    Internet Speed
    57/18 Mbps
    Browser
    Vivaldi
    Antivirus
    Kaspersky Total Security

rpetruzz

Member
Local time
12:21 AM
Posts
2
One other thought, in the format cells dialog there is an option for custom formats. Would this help??

1637433531919.png1637433574810.png
 

My Computers

System One System Two

  • Operating System
    Windows 10 Pro X64 21H1
    Computer type
    PC/Desktop
    Manufacturer/Model
    DIY Build
    CPU
    AMD Ryzen 7 3800X
    Motherboard
    ASUS ROG STRIX X570-E GAMING
    Memory
    Corsair DDR4-2132 4x16gb Dual Channel
    Graphics Card(s)
    NVIDIA GeForce GTX 1070
    Sound Card
    On motherboard
    Monitor(s) Displays
    2 - NEC PA241W
    Screen Resolution
    1920X1200
    Hard Drives
    2 - Corsair Force MP600 M.2 2280 1TB PCI-Express Gen 4.0 x4 NVMe
    PSU
    850 Watt Corsair® AX850 80+
    Case
    Maingear ATX
    Cooling
    Wraith Prism cooler
    Keyboard
    Microsoft Sidewinder
    Mouse
    Logitech
    Internet Speed
    Spectrum - 200mbs
    Browser
    Chrome
    Antivirus
    Windows Defender
  • Operating System
    Windows 10 Pro X64 21H1
    Computer type
    Laptop
    Manufacturer/Model
    Maingear Element
    CPU
    Intel Core i7 9750H
    Motherboard
    Intel LAPQC71B
    Memory
    32gb - Kingston DDR4 1333 Mhz Dual channel
    Graphics card(s)
    NVIDIA GeForce RTX 2070 Max Q
    Sound Card
    On board
    Monitor(s) Displays
    LCD
    Screen Resolution
    1920x1080
    Hard Drives
    2tb INTEL SSD PEKNW020T8
    PSU
    On board
    Case
    Laptop
    Cooling
    Fans
    Mouse
    TeckNet
    Keyboard
    On Board
    Internet Speed
    Spectrum 200mbps
    Browser
    Chrome
    Antivirus
    Windows Defender
Top Bottom