Date Calculation 3

[ousoonerjoe]

Is there a simple way of calculating the last day of the month and displaying in MM/DD/YYYY format? There's a trick, it must also be able to account for year change as well.

for example: 1/10/2008 minus 1 month = 12/10/2008

What i need is the last day of the previous month.

Any thoughts are appreciated.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[ousoonerjoe]

Thanks, strongm. I had completely forgotten about DateSerial. We have some reports that are run at the beginning of the month covering the prior month or quarter. (Accounts love these sort of things. Fortunately they aren't managers or they'd be asking for pie charts to go with them.)

Here's the snip it I used for anyone that needs this same kind of info in the future (NOTE: enddate is passed through the Function):

Dim RptDate As Date
RptDate = DateSerial(Year(enddate), Month(enddate) + 1, 0)
WSheet.Cells(3, 1).Value = "Loss Run Date - " & Format(RptDate, "MM/DD/YYYY")

Thanks again for the input, guys. Was exactly what i was looking for.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[three57m]

<<Is there a simple way of calculating the last day of the month and displaying in MM/DD/YYYY format? There's a trick, it must also be able to account for year change as well.

lastdayofmonth = whatitis
whatitis = istheyearchanged
result = whatitis
display = format "MM/DD/YYYY" for result

 

[SkipVought]

three57m,

What's your point?

All the OPs requirements have been addessed, including the "trick".

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}