Recognise text as date
 

Chaps,

Auto-generated report output gives text values for month and year in
yyyymmformat, e.g. Jan 2007 is 200701

Problem is these are text.

How can I get Excel to recognise this as a date?

I've tried YEAR(the text) and get something hideous.

I've tried LEFT(the text,4) to get the year out as 2007, but this is still
seen as text. e.g. if I try YEAR(the 2007 output), I get 1905...

I really should know better, but have been trying to get this working for
ages.

Any thoughts would be greatly appreciated.

Cheers.

Tom.

e.g. if I try

Recognise text as date
 

Hi Tom

Perhaps:

=TEXT(--A1,"0000-00-\0\1")+0

It does return the date as the first of the month, which i hope is OK
for you.

Hope this helps!

Richard


On 14 Mar, 11:23, mr tom <mr-tom at mr-tom.co.uk.(donotspam) wrote:
Chaps,

Auto-generated report output gives text values for month and year in
yyyymmformat, e.g. Jan 2007 is 200701

Problem is these are text.

How can I get Excel to recognise this as a date?

I've tried YEAR(the text) and get something hideous.

I've tried LEFT(the text,4) to get the year out as 2007, but this is still
seen as text. e.g. if I try YEAR(the 2007 output), I get 1905...

I really should know better, but have been trying to get this working for
ages.

Any thoughts would be greatly appreciated.

Cheers.

Tom.

e.g. if I try

Recognise text as date
 

Thank you. That's ideal.

Tom.

"RichardSchollar" wrote:

Hi Tom

Perhaps:

=TEXT(--A1,"0000-00-\0\1")+0

It does return the date as the first of the month, which i hope is OK
for you.

Hope this helps!

Richard


On 14 Mar, 11:23, mr tom <mr-tom at mr-tom.co.uk.(donotspam) wrote:
Chaps,

Auto-generated report output gives text values for month and year in
yyyymmformat, e.g. Jan 2007 is 200701

Problem is these are text.

How can I get Excel to recognise this as a date?

I've tried YEAR(the text) and get something hideous.

I've tried LEFT(the text,4) to get the year out as 2007, but this is still
seen as text. e.g. if I try YEAR(the 2007 output), I get 1905...

I really should know better, but have been trying to get this working for
ages.

Any thoughts would be greatly appreciated.

Cheers.

Tom.

e.g. if I try