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

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


--
Regards,

Peo Sjoblom



"WCM" wrote in message
...
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

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Peo Sjoblom" wrote in message ...
As long the dates are numerical (like the serial number 39121) it should
convert perfectly fine according to the regional date settings


--
Regards,

Peo Sjoblom



"WCM" wrote in message
...
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

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" wrote:

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


--
Regards,

Peo Sjoblom



"WCM" wrote in message
...
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

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 dataimport external
dataimport 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



--
Regards,

Peo Sjoblom


"WCM" wrote in message
...
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" wrote:

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


--
Regards,

Peo Sjoblom



"WCM" wrote in message
...
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

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

"Peo Sjoblom" wrote:

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 dataimport external
dataimport 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



--
Regards,

Peo Sjoblom


"WCM" wrote in message
...
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" wrote:

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


--
Regards,

Peo Sjoblom



"WCM" wrote in message
...
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

Brilliant...! It worked!

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

"Peo Sjoblom" wrote:

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 dataimport external
dataimport 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



--
Regards,

Peo Sjoblom


"WCM" wrote in message
...
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" wrote:

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


--
Regards,

Peo Sjoblom



"WCM" wrote in message
...
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

Thanks for letting us know it worked


--
Regards,

Peo Sjoblom

"WCM" wrote in message
...
Brilliant...! It worked!

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

"Peo Sjoblom" wrote:

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 dataimport external
dataimport 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



--
Regards,

Peo Sjoblom


"WCM" wrote in message
...
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" wrote:

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


--
Regards,

Peo Sjoblom



"WCM" wrote in message
...
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