• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Custom number format issue|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Custom number format issue|General Excel Questions & Answers|Excel Forum|My Online Training Hub

sale now on

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Custom number format issue
sp_PrintTopic sp_TopicIcon
Custom number format issue
Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 858
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
1
August 24, 2017 - 11:39 pm
sp_Permalink sp_Print

Hello,

When checking the workbook for Excel Dates Displayed in Different Languages I noticed that the custom number format for months did not show correct, so I checked it out. It seems to be that I who use Swedish as Regional language have to use capital M instead of small m when defining the custom number format.

Custom-format-issue.JPGImage Enlarger

Now I wonder why. I have a O365 Home subscription and Excel 2016 MSO (16.0.8326.2076) 32-bit.

When checking one reference article at Microsoft it seems ok to use small letters. But as you can see in the attached picture, I only get 00 as result instead of the months name and its abbreviation. If I change the formatting to capital M then I get correct result. The formula for the January result is now the following: =TEXT($L$4;VLOOKUP($I$4;Table1[[Language]:[Language ID]];3;0)&"MMMM")

If someone knows more about this "issue" then I would be more than happy to get more information about this.

Br,
Anders

sp_PlupAttachments Attachments
  • sp_PlupImage Custom-format-issue.JPG (29 KB)
Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1802
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
2
August 28, 2017 - 2:08 pm
sp_Permalink sp_Print sp_EditHistory

Hi Anders,

Is the Excel version you are using in Swedish or in English?

I know that text function behaves differently if the installed excel version is in a different language, for example, for german version of excel, to format as dd/mm/yyyy you have to use:

=Text(A1,"tt/mm/jjjj"), for french: =Text(A1,"jj/mm/aaaa")

Can you switch from Swedish to English language pack to test if the problem remains?

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 858
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
3
August 29, 2017 - 5:13 am
sp_Permalink sp_Print

Hi Catalin,

Yes, it is a Swedish version. I am using the English language pack though. I will do some test later on with switching Windows language and also try with other regional setting. Can be a good thing to also test using the language tag instead of the id.

Thanks for your support, it is appreciated.

Br,

Anders

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 858
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
4
August 29, 2017 - 6:55 am
sp_Permalink sp_Print

Hi,

The problem seem to be with Windows 10 and display language set to Swedish (and possibly other languages as well). As the pictures shows it works fine when I change Windows display language to English, but if I stick with my native language the formatting for month using small m makes Excel to treat that formatting as minutes instead of month. I also noticed that the formatting where I use capital M works fine with English display language.

Seems that one needs to check this "issue" if you are about to share any date formatted Excel file internationally.

I also noticed another issue, but this time related to the language id. Seems to me it would be better to use the language tag as LCID as some language id's are the same for different languages, for example this one shown in the picture, id 0x1000, or as it is written in Excel, [$-1000]. When I have Swedish as Windows display language I get Swedish result for the name of the day for the Cornish language, just because same language id is used for Åland islands.

So now I know at least why this "issue" occurs. Never seen this problem before though and I have used Excel since 1996.

Br,
Anders

English-W10-display-language.JPGImage Enlarger

Swedish-W10-display-language.JPGImage Enlarger
Issue-with-using-language-id.JPGImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupImage English-W10-display-language.JPG (75 KB)
  • sp_PlupImage Swedish-W10-display-language.JPG (73 KB)
  • sp_PlupImage Issue-with-using-language-id.JPG (93 KB)
Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1802
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
5
August 29, 2017 - 2:30 pm
sp_Permalink sp_Print

Thanks for feedback Anders,

I've seen the TEXT function used in visual basic UDF, to overcome regional settings (as there can be many variations, as described in previous message for german and french languages), in VB all the formats are in english only.

Cheers,

Catalin

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 170
Currently Online: Richard Adams
Guest(s) 7
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 858
Velouria: 580
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 214
A.Maurizio: 202
Aye Mu: 201
Jessica Stewart: 189
Newest Members:
Richard Adams
Vincent Starcevich
Patrick Lawrence
Louise Bell
GIJO GEORGE
Kumud Patel
Atos Franzon
Andrew MacDonald
Artie Ball
Jenn Cain
Forum Stats:
Groups: 3
Forums: 24
Topics: 6079
Posts: 26684

 

Member Stats:
Guest Posters: 49
Members: 31570
Moderators: 2
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.