How to convert date from Indian format to US

[kganna]

The indian format date I am have to convert is in the form of text like 1.12.2012 (day.month.year). How to convert this to the us format (mm/dd/yy)

[Moo the Dog]

You can try this, although I am sure there is likely a shorter method:

If your Indian formatted date is in A1, put this in another cell:

=DATE(RIGHT(A1,4),MID(A1,FIND(".",A1)+1,FIND(".",A1,FIND(".",A1))),LEFT(A1,FIND(".",A1)))

- Moo

[benishiryo]

hi kganna, an alternative would be:
=SUBSTITUTE(A1,".","/")+0
format to the date format you desire

[icestationzbra]

try:

Please Login or Register  to view this content.

in case you need to only display the date in the cell (in this case, date is being output in "text" format).

or

Please Login or Register  to view this content.

then select a specific Date format to apply (in this case, date is being output in "number" format).

[Moo the Dog]

I tried what benishiryo posted, but it was returning the wrong dates. Haven't tried ice's yet though.

[daddylonglegs]

=DATE(RIGHT(A1,4),MID(A1,FIND(".",A1)+1,FIND(".",A1,FIND(".",A1))),LEFT(A1,FIND(".",A1)))

That works for me - but I think you can shorten to

=DATE(RIGHT(A1,4),MID(A1,FIND(".",A1)+1,2),LEFT(A1,2))

[Moo the Dog]

@daddy

Thanks, I knew it could be shortened...

- Moo

[benishiryo]

@Moo, yeah you are right. yours & Daddylonglegs' solution could be more universal. tried changing mine to the US settings (M/D/Y) & it didnt work.

[Marcol]

@ benishiryo

Ahh ... the joys of Dates, Regional settings, etc in Excel.

Your formula seems fine to me, but Regional settings could come into play for others.
I'm not sure you are comparing like for like when you say it fails.

Given this from Wikipedia

In India the DD-MM-YY is the predominant short form of the numeric date usage in India. Almost all government documents need to be filled up in the DD-MM-YYYY format. An example of DD-MM-YYYY usage is the passport application form.

Both uses of the expanded form are used in India. The DD MMMM YYYY usage is more prevalent over the MMMM DD, YYYY usage. The MMMM DD, YYYY usage is more prevalent by media publications such as the print version of the Times of India, and The Hindu.

Provided kganna is using an Indian default setting and not Urdu (Pakistan?), or works for the Times of India, and the values are always as shown in post #1, then all suggestions should work as far as I can see.
DLL will be able to correct me if I'm wrong, he's the master with dates.

The attached shows some potential failings of the formulae other than yours.

Take your pick! I'm outa here before DLL thumps me ...

[benishiryo]

@ Marcol
=)
always good to get inputs from you. what i meant was actually changing my Control Panel settings to the US format (m/d/y). so if date is:
31.12.2012
using my formula would make it:
31/12/2012
and it fails because there's no "31" in months, hence giving a VALUE error. i suppose if OP sticks to "dd.mm.yyyy", their methods would be more effective.

[daddylonglegs]

Yeah, I was assuming that as kganna's location is shown as Montana he would have US date settings, i.e. a default date setting of m/d/yyyy. If that's the case then 1.12.2012 would be converted to 1/12/2012 by substituting "."s for "/"s and that will be interpreted as 12th January rather than 1st December as required.

The formulas that Moo and I suggested shouldn't have that drawback - because they use DATE function they will work with either US or UK date settings

.....there is one potential drawback, though. If you use

=DATE(RIGHT(A1,4),MID(A1,FIND(".",A1)+1,2),LEFT(A1,2))

and A1 contains 1.1.2012 then the formula is exploiting the fact that "." is the decimal separator, so where LEFT(A1,2) returns "1." that will be interpreted as 1.0 and 1 is used as required. If regional settings don't have . as decimal separator, French settings for example, then that formula doesn't work (even using semi-colon argument separators in the formula).

If you wanted it to work for that scenario too, then you need to make sure that the DATE function takes only the numeric parts of the string (Moo's formula is closer to that but the third argument of MID still needs a tweak).

It would be useful to know what date formats are possible here - Presumably there might be 1 or 2 digit days but from the one example given it's not clear whether years will always be 4 digits or whether months will always be 2

[Moo the Dog]

Ahhh, the joys of Excel dating...

My wife does not approve. =)