Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically update a cell with a date based on anther cells date | Excel Discussion (Misc queries) | |||
Date Turns Green 90-Days before the date shown in cell | Excel Worksheet Functions | |||
Create a button that will date stamp todays date in a cell | Excel Discussion (Misc queries) | |||
formula to calculate future date from date in cell plus days | Excel Worksheet Functions | |||
Using formulas to determine date in one cell based on date in anot | Excel Worksheet Functions |