Get Last Day of Month  
Author Message
GopinathM





PostPosted: Fri Jan 28 17:03:02 CST 2005 Top

Excel Programming >> Get Last Day of Month

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)

Excel502  
 
 
JimThomlinson





PostPosted: Fri Jan 28 17:03:02 CST 2005 Top

Excel Programming >> Get Last Day of Month CDate takes a single string as it's argument. You want the DateSerial
function. It uses the 3 parts you specified...

HTH



> 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)
>
>
>