Home

UK Date format

%3CLINGO-SUB%20id%3D%22lingo-sub-435744%22%20slang%3D%22en-US%22%3EUK%20Date%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-435744%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20new%20here%20and%20will%20appreciate%20some%20help%20with%20Excel%20Online%3C%2FP%3E%3CP%3EI%20wish%20to%20format%20my%20dates%20in%20a%20single%20column%20as%20dd%2Fmm%2Fyy%20for%20UK%20format.%20My%20Control%20Panel%20options%20are%20set%20to%20locale%20of%20English%20(United%20Kingdom).%3C%2FP%3E%3CP%3EIdeally%20I%20would%20like%20to%20be%20able%20to%20type%2011%2F04%20into%20a%20cell%20which%20should%20appear%20as%2011%2F04%2F19%20but%20it%20instead%20appears%20as%2004%2F11%2F19%20despite%20the%20format%20being%20set%20correctly.%20This%20is%20obviously%20frustrating.%3C%2FP%3E%3CP%3ELooking%20at%20the%20attached%20screenshot%2C%20the%20first%20few%20dates%20in%20the%20column%20are%20set%20to%20text%20format%20because%20I%20was%20experimenting.%20The%20next%20few%20are%20formatted%20as%20short%20date%20UK.%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-435744%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-438929%22%20slang%3D%22en-US%22%3ERe%3A%20UK%20Date%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-438929%22%20slang%3D%22en-US%22%3EDoes%20this%20help%20perhaps%3A%20%3CA%20href%3D%22https%3A%2F%2Fanswers.microsoft.com%2Fen-us%2Fmsoffice%2Fforum%2Fmsoffice_excel-mso_other%2Fexcel-online-how-to-change-regional-settings%2F03862c3c-8f32-42cf-961c-112f0f1fd790%3Fauth%3D1%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fanswers.microsoft.com%2Fen-us%2Fmsoffice%2Fforum%2Fmsoffice_excel-mso_other%2Fexcel-online-how-to-change-regional-settings%2F03862c3c-8f32-42cf-961c-112f0f1fd790%3Fauth%3D1%3C%2FA%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-438979%22%20slang%3D%22en-US%22%3ERe%3A%20UK%20Date%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-438979%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F319219%22%20target%3D%22_blank%22%3E%40Michael188%3C%2FA%3E%26nbsp%3B%2C%20yes%2C%20as%20Jan%20Karel%20answered%20you%20shall%20to%20define%20UK%20locale%20for%20your%20OneDrive%2C%20not%20for%20PC.%20Settings%20are%20different%20depends%20on%20you%20use%20OneDrive%20Personal%20or%20Business%20one.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-443261%22%20slang%3D%22en-US%22%3ERe%3A%20UK%20Date%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-443261%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%20I%20have%20asked%20my%20internal%20IT%20support%20to%20update%20that%20option%20within%20OneDrive%20(which%20is%20currently%20not%20a%20valid%20locale)%20and%20I%20will%20respond%20back%20later%20when%20it%20has%20been%20done.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1014885%22%20slang%3D%22en-US%22%3ERe%3A%20UK%20Date%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1014885%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Sergei%3CBR%20%2F%3E%3CBR%20%2F%3EIn%20my%20hunt%20for%20an%20answer%20re%20an%20almost%20identical%20issue%2C%20I%20have%20seen%20your%20name%20pop%20up%20several%20times%20providing%20a%20successful%20solution%2C%20but%20i%20still%20can't%20get%20this%20to%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20issue%3A%3C%2FP%3E%3CUL%3E%3CLI%3EI%20have%20an%20office%20365%20business%20subscription.%26nbsp%3B%20I%20normally%20open%20the%20spreadsheet%20on%20my%20desktop%20and%20all%20is%20well%20(UK%20date%20format%20works%20perfectly).%26nbsp%3B%20However%2C%20if%20I%20(or%20four%20other%20UK%20users)%20access%20it%20online%3A%3CUL%3E%3CLI%3EDate%20format%20appears%3A%3CUL%3E%3CLI%3Ecorrectly%20(as%20UK%20date%20format)%20in%20cell%3C%2FLI%3E%3CLI%3Eincorrectly%20(as%20US%20date%20format)%20in%20Formula%20Bar%26nbsp%3B%26nbsp%3B%3C%2FLI%3E%3C%2FUL%3E%3C%2FLI%3E%3CLI%3ETyping%3A%3CUL%3E%3CLI%3E1%2F2%20in%20a%20cell%20(i.e.%201st%20Feb%20UK)%2C%20generates%202%2F1%20in%20the%20cell%20(i.e.%202nd%20Jan%20UK).%26nbsp%3B%3C%2FLI%3E%3CLI%3E31%2F10%20in%20a%20cell%20is%20not%20recognised%20as%20a%20date%3C%2FLI%3E%3C%2FUL%3E%3C%2FLI%3E%3C%2FUL%3E%3C%2FLI%3E%3C%2FUL%3E%3CP%3ESo%20far%3A%3C%2FP%3E%3CUL%3E%3CLI%3EI've%20set%20to%20English%20(UK)%2F(UTC%2B00%3A00)%20Dublin%2C%20Edinburgh%2C%20Lisbon%20where%20possible%3A%3CUL%3E%3CLI%3EControl%20Panel%2C%20Region%26nbsp%3B%3C%2FLI%3E%3CLI%3EOne%20Drive%2C%20Regional%20Settings%3C%2FLI%3E%3CLI%3EMicrosoft%20365%20admin%20center%2C%20settings%3C%2FLI%3E%3CLI%3EExcel%20-%20Cell%20Number%20Format%3C%2FLI%3E%3C%2FUL%3E%3C%2FLI%3E%3CLI%3EI've%20also%20tried%20using%20custom%20dates%20in%20Excel%20desktop%20to%20no%20avail%3C%2FLI%3E%3C%2FUL%3E%3CP%3EDo%20you%20have%20any%20other%20suggestions%3F%3CBR%20%2F%3E%3CBR%20%2F%3ECheers%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EHelen%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1014904%22%20slang%3D%22en-US%22%3ERe%3A%20UK%20Date%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1014904%22%20slang%3D%22en-US%22%3EWhat%20is%20your%20browser%20set%20to%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1014952%22%20slang%3D%22en-US%22%3ERe%3A%20UK%20Date%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1014952%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EI'm%20using%20Chrome%20and%20its%20set%20to%3A%26nbsp%3B%3CBR%20%2F%3ELanguage%3A%20english%20(UK)%3CBR%20%2F%3ERegion%3A%20United%20Kingdom%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1015019%22%20slang%3D%22en-US%22%3ERe%3A%20UK%20Date%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1015019%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F460891%22%20target%3D%22_blank%22%3E%40helen42%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnd%20the%20same%20is%20for%20OneDrive%20setting%3F%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20839px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157690iFFDB77AA295B342E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ETime%20zone%20is%20not%20critical%20from%20this%20point%20of%20view.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20is%20what%20I%20have%20with%20above%20OneDrive%20for%20Business%20settings%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20168px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157691i835ACDCB114D6A10%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eand%20two%20digits%20date%202%2F1%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20163px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157692iF0CA892DC3542FE0%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIMHO%2C%20itself%20browser%20settings%20and%20client%20Windows%20settings%20shall%20not%20affect.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1017591%22%20slang%3D%22en-US%22%3ERe%3A%20UK%20Date%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1017591%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYip%2C%20ONEDRIVE%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157910i5E46380F82731BA5%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI've%20also%20set%20Sharepoint%20and%20MS%20Teams%20to%20UK.%3CBR%20%2F%3E%3CBR%20%2F%3EEXCEL%20ONLINE%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157911i515DCB6F981909C8%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_1.png%22%20title%3D%22clipboard_image_1.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EEXCEL%20DESKTOP%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F157914i7B9F6D15D8CBC9B8%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22clipboard_image_2.png%22%20title%3D%22clipboard_image_2.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Michael188
New Contributor

I am new here and will appreciate some help with Excel Online

I wish to format my dates in a single column as dd/mm/yy for UK format. My Control Panel options are set to locale of English (United Kingdom).

Ideally I would like to be able to type 11/04 into a cell which should appear as 11/04/19 but it instead appears as 04/11/19 despite the format being set correctly. This is obviously frustrating.

Looking at the attached screenshot, the first few dates in the column are set to text format because I was experimenting. The next few are formatted as short date UK.

Thank you!

9 Replies

@Michael188 , yes, as Jan Karel answered you shall to define UK locale for your OneDrive, not for PC. Settings are different depends on you use OneDrive Personal or Business one.

@Sergei Baklan 

@Jan Karel Pieterse 

Thank you. I have asked my internal IT support to update that option within OneDrive (which is currently not a valid locale) and I will respond back later when it has been done.

@Sergei Baklan 

 

Hi Sergei

In my hunt for an answer re an almost identical issue, I have seen your name pop up several times providing a successful solution, but i still can't get this to work.

 

My issue:

  • I have an office 365 business subscription.  I normally open the spreadsheet on my desktop and all is well (UK date format works perfectly).  However, if I (or four other UK users) access it online:
    • Date format appears:
      • correctly (as UK date format) in cell
      • incorrectly (as US date format) in Formula Bar  
    • Typing:
      • 1/2 in a cell (i.e. 1st Feb UK), generates 2/1 in the cell (i.e. 2nd Jan UK). 
      • 31/10 in a cell is not recognised as a date

So far:

  • I've set to English (UK)/(UTC+00:00) Dublin, Edinburgh, Lisbon where possible:
    • Control Panel, Region 
    • One Drive, Regional Settings
    • Microsoft 365 admin center, settings
    • Excel - Cell Number Format
  • I've also tried using custom dates in Excel desktop to no avail

Do you have any other suggestions?

Cheers 

Helen

What is your browser set to?

Hi @Jan Karel Pieterse 

I'm using Chrome and its set to: 
Language: english (UK)
Region: United Kingdom

 

 

@helen42 

And the same is for OneDrive setting?

image.png

Time zone is not critical from this point of view.

 

Here is what I have with above OneDrive for Business settings

image.png

and two digits date 2/1

image.png

IMHO, itself browser settings and client Windows settings shall not affect.

@Sergei Baklan 

Yip, ONEDRIVE:

clipboard_image_0.png

I've also set Sharepoint and MS Teams to UK.

EXCEL ONLINE

clipboard_image_1.png

EXCEL DESKTOP

clipboard_image_2.png

@helen42 

To be sure - your file is on OneDrive or on Sharepoint?

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
12 Replies