I'm trying to transform a number "200412" into the date of the last day of
that month. If A1 = "200412", I do below steps. Step 1 works fine, but in
Step 2, I can't figure out a formula to return "12/31/2004" or the last date
of month.

Any help would be appreciated.

Step 1:
In B1, I enter =RIGHT(A1,2) & "/" & (LEFT(A1,LEN(A1)-2))
which gives me "12/2004"

Step 2:
In C1, I'm trying to use the formula

EndOfMonth = DateSerial(Year(B1),Month(B1)+1,0)
to return "12/31/2004" but I get the error #NAME?

Assuming your year portion is always 4 digits

=DATE(INT(A1 / 100), MOD(A1, 100) + 1, 0)

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"scott" wrote in message
...
I'm trying to transform a number "200412" into the date of the last day of
that month. If A1 = "200412", I do below steps. Step 1 works fine, but in
Step 2, I can't figure out a formula to return "12/31/2004" or the last
date of month.

Any help would be appreciated.

Step 1:
In B1, I enter =RIGHT(A1,2) & "/" & (LEFT(A1,LEN(A1)-2))
which gives me "12/2004"

Step 2:
In C1, I'm trying to use the formula

EndOfMonth = DateSerial(Year(B1),Month(B1)+1,0)
to return "12/31/2004" but I get the error #NAME?

Change Step1 to
=DATEVALUE(RIGHT(A1,2) & "/" & (LEFT(A1,LEN(A1)-2)))

--
Regards,
Tom Ogilvy

"scott" wrote in message
...
I'm trying to transform a number "200412" into the date of the last day of
that month. If A1 = "200412", I do below steps. Step 1 works fine, but in
Step 2, I can't figure out a formula to return "12/31/2004" or the last
date
of month.

Any help would be appreciated.

Step 1:
In B1, I enter =RIGHT(A1,2) & "/" & (LEFT(A1,LEN(A1)-2))
which gives me "12/2004"

Step 2:
In C1, I'm trying to use the formula

EndOfMonth = DateSerial(Year(B1),Month(B1)+1,0)
to return "12/31/2004" but I get the error #NAME?

And for step 3, use Date, not DateSerial
= DATE(YEAR(B1),MONTH(B1)+1,0)

--
Regards,
Tom Ogilvy

"scott" wrote in message
...
I'm trying to transform a number "200412" into the date of the last day of
that month. If A1 = "200412", I do below steps. Step 1 works fine, but in
Step 2, I can't figure out a formula to return "12/31/2004" or the last
date
of month.

Any help would be appreciated.

Step 1:
In B1, I enter =RIGHT(A1,2) & "/" & (LEFT(A1,LEN(A1)-2))
which gives me "12/2004"

Step 2:
In C1, I'm trying to use the formula

EndOfMonth = DateSerial(Year(B1),Month(B1)+1,0)
to return "12/31/2004" but I get the error #NAME?

Thanks again. I think I'm almost through getting my spreadsheet formatted
for export to access db.


"Tom Ogilvy" wrote in message
...
And for step 3, use Date, not DateSerial
= DATE(YEAR(B1),MONTH(B1)+1,0)

--
Regards,
Tom Ogilvy

"scott" wrote in message
...
I'm trying to transform a number "200412" into the date of the last day
of
that month. If A1 = "200412", I do below steps. Step 1 works fine, but in
Step 2, I can't figure out a formula to return "12/31/2004" or the last
date
of month.

Any help would be appreciated.

Step 1:
In B1, I enter =RIGHT(A1,2) & "/" & (LEFT(A1,LEN(A1)-2))
which gives me "12/2004"

Step 2:
In C1, I'm trying to use the formula

EndOfMonth = DateSerial(Year(B1),Month(B1)+1,0)
to return "12/31/2004" but I get the error #NAME?

Scott,

The following copes with single digit months e.g.. 20042 or 200403 return
last date of February - 29/02/2004 (uk format date).

=Date(left(a1,4),mid(a1,5,2)+1,0)

"scott" wrote in message
...
Thanks again. I think I'm almost through getting my spreadsheet formatted
for export to access db.


"Tom Ogilvy" wrote in message
...
And for step 3, use Date, not DateSerial
= DATE(YEAR(B1),MONTH(B1)+1,0)

--
Regards,
Tom Ogilvy

"scott" wrote in message
...
I'm trying to transform a number "200412" into the date of the last day
of
that month. If A1 = "200412", I do below steps. Step 1 works fine, but
in
Step 2, I can't figure out a formula to return "12/31/2004" or the last
date
of month.

Any help would be appreciated.

Step 1:
In B1, I enter =RIGHT(A1,2) & "/" & (LEFT(A1,LEN(A1)-2))
which gives me "12/2004"

Step 2:
In C1, I'm trying to use the formula

EndOfMonth = DateSerial(Year(B1),Month(B1)+1,0)
to return "12/31/2004" but I get the error #NAME?