Extracting a Date from a YYYYMM number
 

Hi Guys

I have a column of dates in the format YYYYMM and I want to extract a date
from this. Specifically the last day of the month mentioned
I was thinking =TEXT(MID(A22,5,2),"MMM") would return me Feb as it is
pointing at the '02' the second month. But it returns 'Jan', presumbly
becausing it is picking up 2nd of Jan 1900.

Then I was going to use something like ="MONTH(E2)+1" to give me the last
day of the month. Didn't work out though....

Thanks for reading this far, and for any help you might be able to give me

Matt

Extracting a Date from a YYYYMM number
 

Try this:

With
A YYYYMM value in A1

B1: =DATE(LEFT(D13,4),RIGHT(D13,2)+1,0)
Format B1 as a date

If A1: 200603
B1 returns 03/31/2006

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Matt" wrote:

Hi Guys

I have a column of dates in the format YYYYMM and I want to extract a date
from this. Specifically the last day of the month mentioned
I was thinking =TEXT(MID(A22,5,2),"MMM") would return me Feb as it is
pointing at the '02' the second month. But it returns 'Jan', presumbly
becausing it is picking up 2nd of Jan 1900.

Then I was going to use something like ="MONTH(E2)+1" to give me the last
day of the month. Didn't work out though....

Thanks for reading this far, and for any help you might be able to give me

Matt

Extracting a Date from a YYYYMM number
 

=DATE(YEAR(A22),MONTH(A22)+1,0)

this formula will give the last day of the current month in a cell A22.

When you try using the TEXT function you are looking at the serial number of
the date.

"Matt" wrote:

Hi Guys

I have a column of dates in the format YYYYMM and I want to extract a date
from this. Specifically the last day of the month mentioned
I was thinking =TEXT(MID(A22,5,2),"MMM") would return me Feb as it is
pointing at the '02' the second month. But it returns 'Jan', presumbly
becausing it is picking up 2nd of Jan 1900.

Then I was going to use something like ="MONTH(E2)+1" to give me the last
day of the month. Didn't work out though....

Thanks for reading this far, and for any help you might be able to give me

Matt

Extracting a Date from a YYYYMM number
 

Wouldn't it be nice if the formula I posted actually referenced cell A1?

With
A YYYYMM value in A1

B1: =DATE(LEFT(A1,4),RIGHT(A1,2)+1,0)
Format B1 as a date

------------------
Instead of B1: =DATE(LEFT(D13,4),RIGHT(D13,2)+1,0)
------------------

***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Try this:

With
A YYYYMM value in A1

B1: =DATE(LEFT(D13,4),RIGHT(D13,2)+1,0)
Format B1 as a date

If A1: 200603
B1 returns 03/31/2006

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Matt" wrote:

Hi Guys

I have a column of dates in the format YYYYMM and I want to extract a date
from this. Specifically the last day of the month mentioned
I was thinking =TEXT(MID(A22,5,2),"MMM") would return me Feb as it is
pointing at the '02' the second month. But it returns 'Jan', presumbly
becausing it is picking up 2nd of Jan 1900.

Then I was going to use something like ="MONTH(E2)+1" to give me the last
day of the month. Didn't work out though....

Thanks for reading this far, and for any help you might be able to give me

Matt

Extracting a Date from a YYYYMM number
 

Thanks Ron

That's just what I needed to crack the problem. In full I used

="Year to "&TEXT(DATE(LEFT(A1,4),RIGHT(A1,2)+1,0),"DD")& "
"&TEXT(DATE(LEFT(A1,4),RIGHT(A1,2)+1,0),"MMM") &"
"&TEXT(DATE(LEFT(A1,4),RIGHT(A1,2)+1,0),"yyyy" )

Cheers

Matt

"Ron Coderre" wrote:

Wouldn't it be nice if the formula I posted actually referenced cell A1?

With
A YYYYMM value in A1

B1: =DATE(LEFT(A1,4),RIGHT(A1,2)+1,0)
Format B1 as a date

------------------
Instead of B1: =DATE(LEFT(D13,4),RIGHT(D13,2)+1,0)
------------------

***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Try this:

With
A YYYYMM value in A1

B1: =DATE(LEFT(D13,4),RIGHT(D13,2)+1,0)
Format B1 as a date

If A1: 200603
B1 returns 03/31/2006

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Matt" wrote:

Hi Guys

I have a column of dates in the format YYYYMM and I want to extract a date
from this. Specifically the last day of the month mentioned
I was thinking =TEXT(MID(A22,5,2),"MMM") would return me Feb as it is
pointing at the '02' the second month. But it returns 'Jan', presumbly
becausing it is picking up 2nd of Jan 1900.

Then I was going to use something like ="MONTH(E2)+1" to give me the last
day of the month. Didn't work out though....

Thanks for reading this far, and for any help you might be able to give me

Matt

Extracting a Date from a YYYYMM number
 

Maybe you'd like to use something like this, instead?:

="Year to "&TEXT(DATE(LEFT(A1,4),RIGHT(A1,2)+1,0),"dd mmm yyyy")

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Matt" wrote:

Thanks Ron

That's just what I needed to crack the problem. In full I used

="Year to "&TEXT(DATE(LEFT(A1,4),RIGHT(A1,2)+1,0),"DD")& "
"&TEXT(DATE(LEFT(A1,4),RIGHT(A1,2)+1,0),"MMM") &"
"&TEXT(DATE(LEFT(A1,4),RIGHT(A1,2)+1,0),"yyyy" )

Cheers

Matt

"Ron Coderre" wrote:

Wouldn't it be nice if the formula I posted actually referenced cell A1?

With
A YYYYMM value in A1

B1: =DATE(LEFT(A1,4),RIGHT(A1,2)+1,0)
Format B1 as a date

------------------
Instead of B1: =DATE(LEFT(D13,4),RIGHT(D13,2)+1,0)
------------------

***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Try this:

With
A YYYYMM value in A1

B1: =DATE(LEFT(D13,4),RIGHT(D13,2)+1,0)
Format B1 as a date

If A1: 200603
B1 returns 03/31/2006

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Matt" wrote:

Hi Guys

I have a column of dates in the format YYYYMM and I want to extract a date
from this. Specifically the last day of the month mentioned
I was thinking =TEXT(MID(A22,5,2),"MMM") would return me Feb as it is
pointing at the '02' the second month. But it returns 'Jan', presumbly
becausing it is picking up 2nd of Jan 1900.

Then I was going to use something like ="MONTH(E2)+1" to give me the last
day of the month. Didn't work out though....

Thanks for reading this far, and for any help you might be able to give me

Matt

Extracting a Date from a YYYYMM number
 

Much Tidier!

Thanks again Ron


"Ron Coderre" wrote:

Maybe you'd like to use something like this, instead?:

="Year to "&TEXT(DATE(LEFT(A1,4),RIGHT(A1,2)+1,0),"dd mmm yyyy")

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Matt" wrote:

Thanks Ron

That's just what I needed to crack the problem. In full I used

="Year to "&TEXT(DATE(LEFT(A1,4),RIGHT(A1,2)+1,0),"DD")& "
"&TEXT(DATE(LEFT(A1,4),RIGHT(A1,2)+1,0),"MMM") &"
"&TEXT(DATE(LEFT(A1,4),RIGHT(A1,2)+1,0),"yyyy" )

Cheers

Matt

"Ron Coderre" wrote:

Wouldn't it be nice if the formula I posted actually referenced cell A1?

With
A YYYYMM value in A1

B1: =DATE(LEFT(A1,4),RIGHT(A1,2)+1,0)
Format B1 as a date

------------------
Instead of B1: =DATE(LEFT(D13,4),RIGHT(D13,2)+1,0)
------------------

***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Try this:

With
A YYYYMM value in A1

B1: =DATE(LEFT(D13,4),RIGHT(D13,2)+1,0)
Format B1 as a date

If A1: 200603
B1 returns 03/31/2006

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Matt" wrote:

Hi Guys

I have a column of dates in the format YYYYMM and I want to extract a date
from this. Specifically the last day of the month mentioned
I was thinking =TEXT(MID(A22,5,2),"MMM") would return me Feb as it is
pointing at the '02' the second month. But it returns 'Jan', presumbly
becausing it is picking up 2nd of Jan 1900.

Then I was going to use something like ="MONTH(E2)+1" to give me the last
day of the month. Didn't work out though....

Thanks for reading this far, and for any help you might be able to give me

Matt