How To Convert A Foreign Month In A Date String Into English

excel date format different from regional settings
how to change date language in excel
how to change indian date format to us in excel
excel date language
how to change month and date in excel
excel formula to detect language
excel text function language
change language in excel cell

The below answer to another thread seems to make a start on what I think I need, but I am having difficulties in implementing it.

The best library for that purpose would probably be Globalize. It allows you to specify the locale (it is called culture but it is actually the same), the format (built-in or your own) and actually parse string to given date:

var dateString = "lunes, 29 de agosto de 2011"; // seems like long date format in Spanish var date = Globalize.parseDate( dateString, "D", "es" );

You would need to attach appropriate culture file as well as reference to Globalize to make it work. Please mind that cultures are already defined in the library, so don't be afraid of the message on the web page, you actually don't need .Net.

If someone could turn this into a working example I could then take this and adjust to what I need.

However ... does anyone know of an alternative method?

My string for example is:

"Set 24, 2012 20:40:20" which has the month in Romanian.

I need it to be

"Sep 24, 2012 20:40:20" which has the month in English.

I need a solution that can take the language code (ie en, es, pl, ru, ro etc) and convert the month in the string (needs to be able to handle all 12 months, not just the one in the above string) from the foreign language to English.

Any speedy help would be most appreciated so that I can get my site back up and running at full speed again.

If you require any further information from me please ask :)

Regards Ross

@Jukka K. Korpela

I have this:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "">
<html xmlns="">
<title>Testing Globalize</title>
<meta name="keywords" content="Globalize, Testing">
<meta name="description" content="Trying to get globalize to work.">
<script src=""></script>
<script src=""></script>
var monthString = 'sep'; // replace by code that extracts the string
var lang = 'ro';          // replace by code that picks up the right language
var month = Globalize.parseDate(monthString, 'MMM', lang);
if(month) {
  document.write(Globalize.format(month, 'MMM', 'en'));
} else {
  alert('Unrecognized month: ' + monthString);

But it doesnt work when I test in "" please help :)

I think u could use datejs for your purpose. It is capable of parsing your date data in any language available (150+ as the state) via a culturefile and then save it in english or any other language you desire.

Have a look :

Excel Dates Displayed in Different Languages • My Online Training , Where [$-0809] is the language ID for English, and dddd tells Excel to covert This translates the 'Day' in the first table and the 'Month' in the second Note: Excel can convert dates into other languages, but it can't convert words. get historical stock prices and foreign currency rates including open, high,  You could use the DateTime button. Select "String to Date/Time format" and the under format select "Custom". Where it says "Specify the format of the incoming string field", type in Mon. The out put will be in date format, you could use substring to extract month number from the date.

The way to do such things in Globalize.js is to read the string using one locale, write it using another locale. You need to check the success of reading by some test that checks against null value (since this value signals an error). Like this:

<script src="globalize.js"></script>
<script src="globalize.cultures.js"></script>
var monthString = 'sep.'; // replace by code that extracts the string
var lang = 'ro';          // replace by code that picks up the right language
var month = Globalize.parseDate(monthString, 'MMM', lang);
if(month) {
  document.write(Globalize.format(month, 'MMM', 'en'));
} else {
  alert('Unrecognized month: ' + monthString);

This parses just the month abbreviation, or "short name" of month.

The problem is with the variation inside cultures. For example, for Romanian language, the short name of September is "sep." in Globalize.js data (mainly based on .NET data; Datejs is probably based on the same data here) but "sept." in CLDR data, and your example mentions "Set"!

Globalize.js reads names case-insensitively, but otherwise it uses the exact string in its locale definitions (e.g., "sep" won’t do if the definition says "sep."). There are various ways around this. At the simplest, edit the locale definitions in Globalize.js to match those used in your data. Alternatively, you can create an alternate locale (a variant) with different month names and parse the name using it if it does not parse according to the basic locale. So this is a way to allow, on input, different sets of month names for a language.

Of course, if it’s only a matter of mapping short month names from different languages into English, you could simply write the code for it directly. But you would still need to get the names into your objects from somewhere – from .NET, from CLDR, or from your actual data.

Specifying a Language for the TEXT Function (Microsoft Excel), He would like the months returned in English instead, and wonders how he can instruct You can easily format a cell to display a date in any language; follow these steps: Note that the bracketed code is within the format string, and the code itself is out a cell that contains a foreign language from the English translation. The dates are written in dd/mm/yyyy format. 1. The way to change this is to take the string of text, for example, 01/10/2014 and convert it to 10/01/2014. Then plug that into the Date() function in Excel. Even Excel is complaining about this example. It tells you that it has stored the text as a number (Excel considers dates as numbers.). 2.

I've stumble upon this difficulty back in 2013, and did work on a small utility to address the problem.

I published my v3 yesterday :

It supports 69 languages, and you don't have to know the language of your month name to get it's number. It's based on a re-usable standalone JSON tree structure for the data and a small tree-walking loop to find the results.

To use it, simply include both the data and the tree-walker files in a web page :

<script src="month_nb_json.js" type="text/javascript"></script>
<script src="month_nb.js" type="text/javascript"></script>

Then you'll be able to use it :

>> month_nb('août');

It's part of my project, which got some funding and that I'll continue to work on for at least the next 6 years (started in 2013…).

Else, it exists which is a human contributed translation approach.

Date formats, How do I prepare my web pages to display varying international date English web page from a web site in Germany that contains the date 03/04/02 (Some prefer to modify ISO 8601 by using an abbreviation for the month to You'll have to do your own mapping of the accepted languages value to a date format string. 1# selected the cells you want to change as month format. 2# right click on the selected cells and click on “Format cells…”. 3# Select “Custom” under “Number” tab, then type “mmm” or “mmmm” type. 4# you will see that the date value is changed to month name.

Use international date format (ISO), W3C QA - How and why to use international date format in your document. example, because the date's interpretation will be different from one country to another. for example will be easy to understand for any English-speaking audience. YYYY is the year [all the digits, i.e. 2012]; MM is the month [01 (​January) to 12  C# DateTime is a struct type, which is mostly used in applications to manage date, date-time, time data types. Most of time, we get a date in form of a string and we usually need to parse to a DateTime object to perform some operations like date difference, weekday, month name, formatting and so on.

How to: fix spreadsheet dates that are in both US and UK formats , The numbers are right-aligned; the strings of characters are Converting the spreadsheet from US to UK formatting would solve the dates as a day not a month); but not those stored as US dates: 03/01 will still be stored as 'March 1'. and therefore date formatting – settings to UK English or equivalent,  datetime is an expression that evaluates to date or datetime value that you want to convert to a string sytle specifies the format of the date. The value of style is a number predefined by SQL Server.

dateparser – python parser for human readable dates, Generic parsing of relative dates like: '1 min ago' , '2 weeks ago' , '3 months, The parser applies formats one by one, taking into account the detected If locales are not given, languages and region are used to construct locales for translation. The parser uses locales to translate date string. region (str|unicode) – A  Microsoft Excel's TEXT function can help you to convert a date to its corresponding month name or weekday name easily. In a blank cell, please enter this formula =TEXT (A2,"mmmm"), in this case in cell C2., and press the Enter key. And then drag this cell's AutoFill handle to the range as you need. And the date have been converted to month name.

  • Please change your title to reflect the question.
  • Welcome to StackOverflow. Please edit your question and give it a meaningful title. "Hi all I came across this" is absolutely meaningless, and provides no information that people can use to see if they can provide help to you or not. Your title should actually ask a question, and the question text then provides explanation and context. Thanks.
  • And then please update the body of your post to make it clearer just what exactly the question is...
  • Apologies I didnt realise that the title was taken from the first line of the body text that I typed, will try to update now.
  • Hopefully this is better?? If not apologies and I will try again.
  • please can you give me an idea of how to implement this. :)
  • Globalize is a bit more flexible, for it allows more than just formatting and parsing dates...
  • I have used your help but got stuck I have put what I have done as part of my original question ... can you help me get it working? PLEASE :)
  • You need to download Globalize.js (and then upload it onto your server when you are about to put your page into production). Moreover, as I wrote, Globalize recognizes "sep." (not "sep") as the Romanian name for September – though you can tweak this by editing the culture definition.
  • @ Jukka K. Korpela , I was wrong that language I mentioned is Portuguese. I work with a lot of language versions of a website a number of which have just updated their date from numeric to alphanumeric. Surely if I am linking to the code online somewhere then it should work??? Which is why I think its in the code?
  • You are not linking to the code online somewhere. You are linking to HTML pages that contain information about the code. The code is designed to be used so that you download it, instead of referring to remotely hosted code.
  • Thank you I got it working:) I'm getting closer to what I need but now that I have tried to pull the month from the string that I have and it stops working do you why? I am using: var myString = 'Set 24, 2012 20:40:20'; var monthString = myString.slice(0,3);