Converting text to Date format

[rangequestion]

Hi,

I have a column of cells with the following text format, "12/17/2015 12:00:00 AM". How can I convert this to a date with only the month and year and to ensure that Excel reads it as a date?

thanks

 

[Scott T]

Try, where A1 has the date text

=DATEVALUE(A1)

 

[bellman101]

If by only the month and year you mean the first of the month, then try

=DATE(RIGHT(LEFT(A1,FIND(" ",A1)-1),4),SUBSTITUTE(LEFT(A1,2),"/",""),1)

where A1 is a text date cell.

This will account for the format m/d/yyyy as well as mm/dd/yyyy.

Cheers
JB

 

[gallen]

Hi You will need to format the result of DATEVALUE:

=TEXT(DATEVALUE(A1),"mm/dd")

You will need to do this in an empty column then copy that column over the column you are converting...

 

[bellman101]

Hi You will need to format the result of DATEVALUE:

=TEXT(DATEVALUE(A1),"mm/dd")

You will need to do this in an empty column then copy that column over the column you are converting...

but then it won't be seen as a date will it?

Surely it would just be Scott's answer, with cell formatting as custom number format mm/yy or mmm yyyy​ would it not?

 

[rangequestion]

but then it won't be seen as a date will it?

Surely it would just be Scott's answer, with cell formatting as custom number format mm/yy or mmm yyyy​ would it not?

None of these seem to work....

 

[rangequestion]

I'm wondering if it doesn't work because the original cell I'm trying to extract is set in a custom format, not date.

 

[Scott T]

None of these seem to work....

What is happening? Are you getting an error?

If you give datevalue an excel date instead of text you will get a Value error.

 

[rangequestion]

What is happening? Are you getting an error?

If you give datevalue an excel date instead of text you will get a Value error.

I get a #VALUE error with every option that was posted here.

 

[Scott T]

Are you sure your date is text?

If this returns true then you have a Excel date.

=ISNUMBER(A1)