Get Last Day of Month

S

scott

Below I have code that finds a date in a cell like "200412". The line that
sets "sDateRange" returns "12/1/2004" which is correct. My problem is the
last line that sets sLastDayMonth variable.

For some reason, sLastDayMonth returns "1/12/1900" but I want it to return
"12/31/2004".
What am I doing wrong?

Dim sDateRange As Date, sLastDayMonth As Date, cell As Range

Set cell = FindCell("To Period", Sheets(1).Cells).Offset(0, 2)
sDateRange = CDate(Right(cell.Value, 2) & "/" & (Left(cell.Value,
Len(cell) - 2)))
sLastDayMonth = CDATE(YEAR(sDateRange),MONTH(sDateRange)+1,0)
 
J

Jim Thomlinson

CDate takes a single string as it's argument. You want the DateSerial
function. It uses the 3 parts you specified...

HTH
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Setting a Date variable 4
Test if Cell is Empty or Doesn't Exist 2
Mid-Month function help 5
Get Last Day of Month 5
End of Quarter Based on Fiscal Year 2
last day of month in prior month 9
Forcing xl2K7 Column Count back to xl2K3 size 3
I'm trying to do a "count" function at the end of a dynamic pivot table 0

Top