|
|
|
Author |
Message |
GopinathM
|
Posted: 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
|
Posted: 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)
>
>
>
|
|
|
|
|
|
|