How to set the cell for date?
 

When I import the data from following link, its date format is mm/dd/yy
instead of dd/mm/yy, therefore, the cell will recognize 03/10/09 to be 3 Oct,
2009 instead of 10 Mar, 2009, however, the recognized error occurs on
03/17/09 for a valid date format. Does anyone have any suggestions how to
retrieve the right date format from importing following link?

http://www.ustreas.gov/offices/domes...te/yield.shtml

Thanks in advance for any suggestions
Eric

How to set the cell for date?
 

Set the format in Windows Regional Options (via the Control Panel) to match
your data.
--
David Biddulph

"Eric" wrote in message
...
When I import the data from following link, its date format is mm/dd/yy
instead of dd/mm/yy, therefore, the cell will recognize 03/10/09 to be 3
Oct,
2009 instead of 10 Mar, 2009, however, the recognized error occurs on
03/17/09 for a valid date format. Does anyone have any suggestions how to
retrieve the right date format from importing following link?

http://www.ustreas.gov/offices/domes...te/yield.shtml

Thanks in advance for any suggestions
Eric

How to set the cell for date?
 

Thank you very much for suggestions
Some worksheet is using the date format dd/mm/yy, therefore I cannot change
the Windows Regional Options for date format into mm/dd/yy.
Do you have any other suggestions?
Thank you very much for any suggestions
Eric

"David Biddulph" wrote:

Set the format in Windows Regional Options (via the Control Panel) to match
your data.
--
David Biddulph

"Eric" wrote in message
...
When I import the data from following link, its date format is mm/dd/yy
instead of dd/mm/yy, therefore, the cell will recognize 03/10/09 to be 3
Oct,
2009 instead of 10 Mar, 2009, however, the recognized error occurs on
03/17/09 for a valid date format. Does anyone have any suggestions how to
retrieve the right date format from importing following link?

http://www.ustreas.gov/offices/domes...te/yield.shtml

Thanks in advance for any suggestions
Eric

How to set the cell for date?
 

Hello Eric,
Here is one workaround:
1) Copy the data from the website and paste into Notepad
2) Save the TXT file
3) In Excel, open the TXT file and a Text Import Wizard will start
a) specify Fixed Width, proceed to Step 3 where you can tell Excel that the
first column is in mm/dd/yyyy format
b) when the TXT file is converted to Excel, the first column will now
display dates in your dd/mm/yyyy format

When, Oh when, will we all use the logical yyyy/mm/dd date format and end
all this US vs Rest-of-World date format problem?

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Eric" wrote in message
...
When I import the data from following link, its date format is mm/dd/yy
instead of dd/mm/yy, therefore, the cell will recognize 03/10/09 to be 3
Oct,
2009 instead of 10 Mar, 2009, however, the recognized error occurs on
03/17/09 for a valid date format. Does anyone have any suggestions how to
retrieve the right date format from importing following link?

http://www.ustreas.gov/offices/domes...te/yield.shtml

Thanks in advance for any suggestions
Eric

How to set the cell for date?
 

Make the change (just temporary), do the import, change it back.

Eric wrote:

Thank you very much for suggestions
Some worksheet is using the date format dd/mm/yy, therefore I cannot change
the Windows Regional Options for date format into mm/dd/yy.
Do you have any other suggestions?
Thank you very much for any suggestions
Eric

"David Biddulph" wrote:

Set the format in Windows Regional Options (via the Control Panel) to match
your data.
--
David Biddulph

"Eric" wrote in message
...
When I import the data from following link, its date format is mm/dd/yy
instead of dd/mm/yy, therefore, the cell will recognize 03/10/09 to be 3
Oct,
2009 instead of 10 Mar, 2009, however, the recognized error occurs on
03/17/09 for a valid date format. Does anyone have any suggestions how to
retrieve the right date format from importing following link?

http://www.ustreas.gov/offices/domes...te/yield.shtml

Thanks in advance for any suggestions
Eric




--

Dave Peterson

How to set the cell for date?
 

Thank everyone very much for suggestions
I need to do it daily, and would like to
change the cell format or
retrieve the data and modify it into a correct date format, rather than swap
the setting everyday.
Do you have any more suggestions?
Thank everyone very much for any suggestions
Eric

"Dave Peterson" wrote:

Make the change (just temporary), do the import, change it back.

Eric wrote:

Thank you very much for suggestions
Some worksheet is using the date format dd/mm/yy, therefore I cannot change
the Windows Regional Options for date format into mm/dd/yy.
Do you have any other suggestions?
Thank you very much for any suggestions
Eric

"David Biddulph" wrote:

Set the format in Windows Regional Options (via the Control Panel) to match
your data.
--
David Biddulph

"Eric" wrote in message
...
When I import the data from following link, its date format is mm/dd/yy
instead of dd/mm/yy, therefore, the cell will recognize 03/10/09 to be 3
Oct,
2009 instead of 10 Mar, 2009, however, the recognized error occurs on
03/17/09 for a valid date format. Does anyone have any suggestions how to
retrieve the right date format from importing following link?

http://www.ustreas.gov/offices/domes...te/yield.shtml

Thanks in advance for any suggestions
Eric




--

Dave Peterson

How to set the cell for date?
 

Did you try Bernard's suggestion?

Eric wrote:

Thank everyone very much for suggestions
I need to do it daily, and would like to
change the cell format or
retrieve the data and modify it into a correct date format, rather than swap
the setting everyday.
Do you have any more suggestions?
Thank everyone very much for any suggestions
Eric

"Dave Peterson" wrote:

Make the change (just temporary), do the import, change it back.

Eric wrote:

Thank you very much for suggestions
Some worksheet is using the date format dd/mm/yy, therefore I cannot change
the Windows Regional Options for date format into mm/dd/yy.
Do you have any other suggestions?
Thank you very much for any suggestions
Eric

"David Biddulph" wrote:

Set the format in Windows Regional Options (via the Control Panel) to match
your data.
--
David Biddulph

"Eric" wrote in message
...
When I import the data from following link, its date format is mm/dd/yy
instead of dd/mm/yy, therefore, the cell will recognize 03/10/09 to be 3
Oct,
2009 instead of 10 Mar, 2009, however, the recognized error occurs on
03/17/09 for a valid date format. Does anyone have any suggestions how to
retrieve the right date format from importing following link?

http://www.ustreas.gov/offices/domes...te/yield.shtml

Thanks in advance for any suggestions
Eric




--

Dave Peterson


--

Dave Peterson

How to set the cell for date?
 

Thank everyone very much for suggesitons
I need to refresh the data daily, therefore Bernard's approach will require
manually processing the data everyday, which is not what I want.

When I import the link, the data format is mm/dd/yyyy
09/01/2009 = 01 Sep, 2009 = 40057
but the date should be read in dd/mm/yy based on my computer setting.
01/09/2009 = 9 Jan, 2009 = 39822

Is there any trick to modify the number 40057 into 39822 for switching the
format from mm/dd to dd/mm?

Thank everyone very much for any suggestions
Eric


"Dave Peterson" wrote:

Did you try Bernard's suggestion?

Eric wrote:

Thank everyone very much for suggestions
I need to do it daily, and would like to
change the cell format or
retrieve the data and modify it into a correct date format, rather than swap
the setting everyday.
Do you have any more suggestions?
Thank everyone very much for any suggestions
Eric

"Dave Peterson" wrote:

Make the change (just temporary), do the import, change it back.

Eric wrote:

Thank you very much for suggestions
Some worksheet is using the date format dd/mm/yy, therefore I cannot change
the Windows Regional Options for date format into mm/dd/yy.
Do you have any other suggestions?
Thank you very much for any suggestions
Eric

"David Biddulph" wrote:

Set the format in Windows Regional Options (via the Control Panel) to match
your data.
--
David Biddulph

"Eric" wrote in message
...
When I import the data from following link, its date format is mm/dd/yy
instead of dd/mm/yy, therefore, the cell will recognize 03/10/09 to be 3
Oct,
2009 instead of 10 Mar, 2009, however, the recognized error occurs on
03/17/09 for a valid date format. Does anyone have any suggestions how to
retrieve the right date format from importing following link?

http://www.ustreas.gov/offices/domes...te/yield.shtml

Thanks in advance for any suggestions
Eric




--

Dave Peterson


--

Dave Peterson

How to set the cell for date?
 

=DATE(YEAR(A1),DAY(A1),MONTH(A1))
--
David Biddulph

Eric wrote:
Thank everyone very much for suggesitons
I need to refresh the data daily, therefore Bernard's approach will
require manually processing the data everyday, which is not what I
want.

When I import the link, the data format is mm/dd/yyyy
09/01/2009 = 01 Sep, 2009 = 40057
but the date should be read in dd/mm/yy based on my computer setting.
01/09/2009 = 9 Jan, 2009 = 39822

Is there any trick to modify the number 40057 into 39822 for
switching the format from mm/dd to dd/mm?

Thank everyone very much for any suggestions
Eric


"Dave Peterson" wrote:

Did you try Bernard's suggestion?

Eric wrote:

Thank everyone very much for suggestions
I need to do it daily, and would like to
change the cell format or
retrieve the data and modify it into a correct date format, rather
than swap the setting everyday.
Do you have any more suggestions?
Thank everyone very much for any suggestions
Eric

"Dave Peterson" wrote:

Make the change (just temporary), do the import, change it back.

Eric wrote:

Thank you very much for suggestions
Some worksheet is using the date format dd/mm/yy, therefore I
cannot change the Windows Regional Options for date format into
mm/dd/yy.
Do you have any other suggestions?
Thank you very much for any suggestions
Eric

"David Biddulph" wrote:

Set the format in Windows Regional Options (via the Control
Panel) to match your data.
--
David Biddulph

"Eric" wrote in message
...
When I import the data from following link, its date format is
mm/dd/yy instead of dd/mm/yy, therefore, the cell will
recognize 03/10/09 to be 3 Oct,
2009 instead of 10 Mar, 2009, however, the recognized error
occurs on 03/17/09 for a valid date format. Does anyone have
any suggestions how to retrieve the right date format from
importing following link?

http://www.ustreas.gov/offices/domes...te/yield.shtml

Thanks in advance for any suggestions
Eric




--

Dave Peterson


--

Dave Peterson