About Text to Date

  • Thread starter Thread starter Sameer
  • Start date Start date
S

Sameer

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.
 
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
 
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
 
CLR said:
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
Click to expand...

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.
 
Sameer said:
CLR wrote:


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.
Click to expand...

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.
 
If you follow the directions in my previous post exactly, they should take
care of the problem you describe......

Vaya con Dios,
Chuck, CABGx3
 
Back
Top