Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
About Text to Date
Hello,
I am having some data in my worksheet which I have pasted from a database. There is one column containing dates. Here in India,we follow dd/mm/yyyy date convention. So when the date is of the format 28/09/2006, i.e. when the day is greater than 12, the entries in the cell are interpreted as text values and they are left aligned. The other dates are treated normally but still Excel treats that the month being day and day being month. This is large database so what to do to treat them as dates in Excel in dd/mm/yyyy. I am using Windows 2000. Do i need to use Control Panel Regional Settings? One solution for me is to extract day, moth and year from text and forming date using these values using the DATE function. Please comment. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
About Text to Date
Format the destination cells as text
Click Edit-Paste Special, and select text and then click on ok Click Data-Text to Columns Select Delimited, and click next Deselect all delimiters, and click next Select Date and DMY, and click finish All entries should now be dates. They might be formatted like mm/dd/yy but you can change the format to this dd/mm/yy "Sameer" wrote: Hello, I am having some data in my worksheet which I have pasted from a database. There is one column containing dates. Here in India,we follow dd/mm/yyyy date convention. So when the date is of the format 28/09/2006, i.e. when the day is greater than 12, the entries in the cell are interpreted as text values and they are left aligned. The other dates are treated normally but still Excel treats that the month being day and day being month. This is large database so what to do to treat them as dates in Excel in dd/mm/yyyy. I am using Windows 2000. Do i need to use Control Panel Regional Settings? One solution for me is to extract day, moth and year from text and forming date using these values using the DATE function. Please comment. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
About Text to Date
Assuming your dates are in column A, put this in B1 and copy down
=IF(ISTEXT(A1),DATE(RIGHT(A1,4),MID(A1,FIND("/",A1,1)+1,2),LEFT(A1,2)),A1) Now all of the dates in column B should be real dates, so then just reformat column B as Custom, DD/MM/YYYY Then you can Copy PasteSpecial on column B and delete column A if you wish. hth Vaya con Dios, Chuck, CABGx3 "Sameer" wrote: Hello, I am having some data in my worksheet which I have pasted from a database. There is one column containing dates. Here in India,we follow dd/mm/yyyy date convention. So when the date is of the format 28/09/2006, i.e. when the day is greater than 12, the entries in the cell are interpreted as text values and they are left aligned. The other dates are treated normally but still Excel treats that the month being day and day being month. This is large database so what to do to treat them as dates in Excel in dd/mm/yyyy. I am using Windows 2000. Do i need to use Control Panel Regional Settings? One solution for me is to extract day, moth and year from text and forming date using these values using the DATE function. Please comment. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
About Text to Date
CLR wrote:
Assuming your dates are in column A, put this in B1 and copy down =IF(ISTEXT(A1),DATE(RIGHT(A1,4),MID(A1,FIND("/",A1,1)+1,2),LEFT(A1,2)),A1) Now all of the dates in column B should be real dates, so then just reformat column B as Custom, DD/MM/YYYY Then you can Copy PasteSpecial on column B and delete column A if you wish. hth Vaya con Dios, Chuck, CABGx3 "Sameer" wrote: Hello, I am having some data in my worksheet which I have pasted from a database. There is one column containing dates. Here in India,we follow dd/mm/yyyy date convention. So when the date is of the format 28/09/2006, i.e. when the day is greater than 12, the entries in the cell are interpreted as text values and they are left aligned. The other dates are treated normally but still Excel treats that the month being day and day being month. This is large database so what to do to treat them as dates in Excel in dd/mm/yyyy. I am using Windows 2000. Do i need to use Control Panel Regional Settings? One solution for me is to extract day, moth and year from text and forming date using these values using the DATE function. Please comment. I was able to create dates from text values by text string manipulation and DATE function. But the values which are originally there in date format are like this. The value is 1/9/2006. The excel is treating it in mm/dd/yyyy format though the date is expected to be in dd/mm/yyyy format. How to let it treat in dd/mm/yyyy? Is there any way to take date and create another date with day and month exchanged? I am not able to do operations on the dates as they are not properly treated by Excel. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
About Text to Date
"Sameer" wrote in message
oups.com... CLR wrote: "Sameer" wrote: Hello, I am having some data in my worksheet which I have pasted from a database. There is one column containing dates. Here in India,we follow dd/mm/yyyy date convention. So when the date is of the format 28/09/2006, i.e. when the day is greater than 12, the entries in the cell are interpreted as text values and they are left aligned. The other dates are treated normally but still Excel treats that the month being day and day being month. This is large database so what to do to treat them as dates in Excel in dd/mm/yyyy. I am using Windows 2000. Do i need to use Control Panel Regional Settings? One solution for me is to extract day, moth and year from text and forming date using these values using the DATE function. Please comment. Assuming your dates are in column A, put this in B1 and copy down =IF(ISTEXT(A1),DATE(RIGHT(A1,4),MID(A1,FIND("/",A1,1)+1,2),LEFT(A1,2)),A1) Now all of the dates in column B should be real dates, so then just reformat column B as Custom, DD/MM/YYYY Then you can Copy PasteSpecial on column B and delete column A if you wish. I was able to create dates from text values by text string manipulation and DATE function. But the values which are originally there in date format are like this. The value is 1/9/2006. The excel is treating it in mm/dd/yyyy format though the date is expected to be in dd/mm/yyyy format. How to let it treat in dd/mm/yyyy? Is there any way to take date and create another date with day and month exchanged? I am not able to do operations on the dates as they are not properly treated by Excel. You may be able to translate the dates using Data/ Text to Columns, and choose the relevant date format in the wizard. Another option may be a helper column with a formula =DATE(YEAR(A1),DAY(A1),MONTH(A1)) To avoid the problem in future, make sure that the date format in your Regional Options (in Windows Control Panel) matches the convention you are trying to use, as you suggested in your original post. -- David Biddulph |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
About Text to Date
If you follow the directions in my previous post exactly, they should take
care of the problem you describe...... Vaya con Dios, Chuck, CABGx3 "Sameer" wrote: CLR wrote: Assuming your dates are in column A, put this in B1 and copy down =IF(ISTEXT(A1),DATE(RIGHT(A1,4),MID(A1,FIND("/",A1,1)+1,2),LEFT(A1,2)),A1) Now all of the dates in column B should be real dates, so then just reformat column B as Custom, DD/MM/YYYY Then you can Copy PasteSpecial on column B and delete column A if you wish. hth Vaya con Dios, Chuck, CABGx3 "Sameer" wrote: Hello, I am having some data in my worksheet which I have pasted from a database. There is one column containing dates. Here in India,we follow dd/mm/yyyy date convention. So when the date is of the format 28/09/2006, i.e. when the day is greater than 12, the entries in the cell are interpreted as text values and they are left aligned. The other dates are treated normally but still Excel treats that the month being day and day being month. This is large database so what to do to treat them as dates in Excel in dd/mm/yyyy. I am using Windows 2000. Do i need to use Control Panel Regional Settings? One solution for me is to extract day, moth and year from text and forming date using these values using the DATE function. Please comment. I was able to create dates from text values by text string manipulation and DATE function. But the values which are originally there in date format are like this. The value is 1/9/2006. The excel is treating it in mm/dd/yyyy format though the date is expected to be in dd/mm/yyyy format. How to let it treat in dd/mm/yyyy? Is there any way to take date and create another date with day and month exchanged? I am not able to do operations on the dates as they are not properly treated by Excel. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
turn off convert text to date (i.e. 4-9 to April 9, 2006) | Excel Worksheet Functions | |||
How do I grab the date from a text string? | Excel Worksheet Functions | |||
NETWORKDAYS - Multiple Date Selection | Excel Discussion (Misc queries) | |||
Macro to convert text to date | Excel Worksheet Functions | |||
EXTRACT TEXT FROM A DATE | Excel Worksheet Functions |