Extracting a Date from a YYYYMM number

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
=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.
 
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
 
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
 
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
 
Much Tidier!

Thanks again Ron


Ron Coderre said:
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
Click to expand...
 
Back
Top