Convert non-date string to a date

[lunker55]

I need to convert a non-date string (09/22/2004) to the date sept 22/04
so I can properly sort.

I have a book that shows converting 20040821 using:
=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

I changed the formula to:
=DATE(LEFT(A1,2),MID(A1,4,2),RIGHT(A1,4))
But this doesn't work for me.

I also tried removing the slashes first.

Any suggestions would be greatly appreciated.


joe

 

[Norman Harker]

Hi lunker55!

Try:

=DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2))

Format

mmm dd/yy

There isn't a format for Sept

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Frank Kabel]

Hi
try
=DATE(Right(A1,4),Left(A1,2),MID(A1,4,2))
the syntax for Date is
DATE(year,month,day)

 

[lunker55]

Thank you Norman. That works, but other ones don't work.
06/10/2004 shows Apr 7/69
I used:
=DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2))

I guess you have to seperate the year first, followed by the month, and
finally the day.

I don't think I am doing this right.

joe

 

[Frank Kabel]

Hi
this works for me. Maybe there are some other characters in your cell
A1. Check the following:
1. =LEN(A1) -> should return 10
2. Change the formula to
=DATE(RIGHT(TRIM(A1),4),LEFT(TRIM(A1),2),MID(TRIM(A1),4,2))

 

[Robert Rosenberg]

You can also simply copy a blank cell, then select the text dates and use
the Paste Special command. In the Paste Special dialog select both the
Values and Add options, then click OK. They may appear as large numbers but
can now be formatted as dates using the Format-->Cells command. The Date
category doesn't have the format you want but you can click the Custom
category and on the Type line enter the following custom date format:

dd/yy

--
_______________________
Robert Rosenberg
R-COR Consulting Services
Microsoft MVP - Excel
http://www.r-cor.com

 

[Norman Harker]

Hi lunker55!

You're doing something wrong:
Entry in A2:
06/10/2004
Formula:
=DATE(RIGHT(A2,4),LEFT(A2,2),MID(A2,4,2))
Returns: Jun 10/04

DATE has the syntax:
=DATE(year,month,day)

Year = The year number as one to four digits
Month = A number representing the month of the year. If month is
greater than 12, month adds that number of months to the first month
in the year specified
Day = A number representing the day of the month. If day is greater
than the number of days in the month specified, day aggregates the
month and year arguments appropriately and day represents the balance
after this aggregation

In each case we have had to "parse" the string text to extract what we
want:

Year:
RIGHT(A2,4)
Takes the last four digits

Month:
LEFT(A2,2)
Takes the first two digits

Day:
MID(A2,4,2)
Takes two digits starting from and including the fourth one.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[lunker55]

Frank:

When I did the =LEN(A1) formula, some results were 10 and others were 5.
But, I can see 10 characters in each cell!

Maybe there is a problem when I convert to an excel file from an html that I
got from a web site.?
joe

 

[Frank Kabel]

Hi
have you checked the format of these cells (goto 'Format - Cells')
maybe a custom format??

 

[lunker55]

That's it!
Half of the cells are text and the other cells are formatted as dates.
Is excel doing this automatically when I convert the html file to excel?
Why can excel only format some of the date cells?

This is getting complicated. Is there a way to check the cell to see if it
is formatted as a date or text, and if text, convert to date format?

Thank you Frank, Norman, and Robert for your help.

joe

 

[Norman Harker]

Hi lunker55!

Those which are 5 in length are date serial numbers
Those which are 10 are text.

So:

=IF(LEN(A1)=5,A1,DATE(RIGHT(A1,4),LEFT(A1,2),MID(A1,4,2)))
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[lunker55]

Thank you Norman!
That works perfectly. Hopefully soon, I'll be able to figure out these
problems without so much help.
Thanks again for your time and patience.
Thanks also-Frank.

joe

 

[lunker55]

The problems continue!
If the cell was an actual date, excel has interpreted the date in reverse.
ie: 06/01/2005 is converted to jan 6/05.
The actual date is jun 1/04.
I can't think of any ways to fix this. I can't reverse the original
date/month cell because it is a date.
I can't change the cells to a text format, because they are turned into the
date numbers.

Any ideas?

joe

 

[Peo Sjoblom]

Use data>text to columns, select next twice and under column data format
select Date and DMY
click finish

 

[lunker55]

Thanks Peo, that did it.

joe

Use data>text to columns, select next twice and under column data format
select Date and DMY
click finish

--

Regards,

Peo Sjoblom

 

[robhenry]

I used an imaginative response to sort out a "date as text" problem.
Copied & pasted into word & used "find & replace" to get rid of all the th, nd,st (in 10th, 2nd, 1st etc). Recopied into excel & corrected spelling errors (Decmber for instance).
It worked - I know it was not your problem but I thought somebody may find it a simple solution.

 

[davib]

Hi,

Ive tried to follow this thread as I have a similar problem but I just cannot get this too work.
I have cause to copy data from a booking system we use and the data comes in as text. I have sorted out all but the date columns.

After pasting I get Mon 27/2/2012. I have used a formula to remove the Mon and I am left with 27/2/2012. This would be perfect except it is still text. I have tried to change the date format but nothing changes. The reason I need to change the data type is we need to use the date field to sort.

So I have 27/2/2012 (string) and I need to end up with 27/2/2012 (date)

I hope that makes sense.

When I apply the forum mentioned in this thread I get 31/1/6044 (or similar)

Thanks for any advice

Cheers