Date format - India/USA

[Guest]

Hi ...

We are having date translation problems between India and the USA. I need
to know how the Excel format *3/14/2001 will behave between an operating
system on mm/dd/yy format and an operating system on dd/mm/yy format.

For example, if the English(USA) date of 08-Feb-07 is fomatted using the
*3/14/2001 format option, it appears as 2/8/07 (serial value '39121') on the
mm/dd/yy system. If I e-mail this Excel file to India (on the dd/mm/yy
system) as an attachment, when the file is opened on their system will it
appear as 8/2/07 with the same serial value of '39121'?

Thanks in advance for your help ...

william 3

 

[Peo Sjoblom]

As long the dates are numerical (like the serial number 39121) it should
convert perfectly fine according to the regional date settings

 

[Ron de Bruin]

See also this article about the ISO date format
http://msdn2.microsoft.com/en-us/library/bb277364.aspx

 

[Guest]

Hi Peo...

Thank you for your response.

I just discovered what the problem is. Both India and USA users are logging
on to an ASP service (through I.E.) whose servers are located in North
America. The Regional Setting on those servers are mm/dd format so both
India and USA users "see" the date as mm/dd. In other words, both see the
string literal "02/08/07" for 02-Feb-07.

But when the India users export ASP dates to a .csv file on their system,
the visual representation of that date remains the same (i.e. '02/08/07',
which is '08-Feb-07', still looks exactly like '02/08/07'). The problem is,
since the India operating system Regional setting is dd/mm, their system
interpets that date to be 02-Aug-07.
So the correct '39121' value becomes '39296' on the India PC.

Is there a 'best practice' to fix this situation?

 

[Peo Sjoblom]

They can import the CSV file either by renaming it to *.txt and the text
import wizard will pop up or they can do it via data>import external
data>import data, for file types put *.* then go to the CSV file, click next
twice and in step 3 under column data format select Date and MDY (always use
the original source date format that would be US in this case) and then
click finish, that should convert it to British dd/mm/yy )

Test it yourself by switching the regional setting to dd/mm/yy, create a
range of dates, then save it as a text file and close it, change back to US
regional setting and import it this way (change it to DMY in step 3) and an
entry like 31/05/07 should be converted to 05/31/07 instead of a text
expression




--
Regards,

Peo Sjoblom

 

[Guest]

Thank you again, Peo. I will try this out and let you know how it goes.

 

[Guest]

Brilliant...! It worked!

This is exactly what we needed. And it makes perfect, logical sense. Thank
you!

 

[Peo Sjoblom]

Thanks for letting us know it worked