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.

 

[SkipVought]

LastDayOfPrevMO = DateSerial(Year(TheDate),Month(TheDate),0)

Skip,

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

 

[SkipVought]

for the current date...

LastDayOfPrevMO = DateSerial(Year(Date),Month(Date),0)

Skip,

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

 

[TysonLPrice]

Format(DateAdd("d", -Day(Date), Date), "MM/DD/YYYY")

 

[SkipVought]

Just keep in mind that Format returns a STRING and not a DATE.

Skip,

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

 

[ousoonerjoe]

Thanks a bunch, Skip. I was able to come up with:

Dim RptDate As Date
RptDate = (Month(Now) & "/1/" & Year(Now))
RptDate = RptDate - 1

but I'll use your's since it's much cleaner.

Thanks again. Have a star.

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

 

[BobRodes]

Another thing to keep in mind about yours, ou, is that it depends on the mmddyyyy format. It wouldn't run properly in Britain, for example, because they use the ddmmyyyy format. So Skip's is not only cleaner, it works better too.

 

[strongm]

And just to show that there's more than one way to skin a cat:

LastDayOfPrevMO = TheDate - Day(TheDate) - 1

 

[SkipVought]

I think that you wanted that to be...

LastDayOfPrevMO = Date - Day(Date)

...and it all makes perfect sense, when you understand that a Date is a NUMBER and Date - 1 is yesterday.

Skip,

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

 

[BobRodes]

Quandoque Bonus Dormitat Homerus...

 

[harebrain]

Yeah, but he spelled clairvoyant wrong.

 

[strongm]

>I think that you wanted

Indeed. No idea how the - 1 got in there ...

 

[SkipVought]

What does Clair have to do with it???

Skip,

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

 

[harebrain]

More to the point, what does Homer's bonus have to do with it?

 

[BobRodes]

In Latin, "bonus" is an adjective, not a noun...

 

[BobRodes]

Well, in this context it is anyway.

 

[harebrain]

Next you'll be telling us that

Levis est fortuna.

doesn't mean "blue jeans are expensive!"

(This has gone way OT.)

 

[tedsmith]

Then how would you find the date of the end of THIS month?

 

[johnwm]

Use 0 for day and Month(Date) + 1 for month

myMonthend = DateSerial(Year(Date),Month(Date)+1,0)

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[strongm]

Then we'd look the solution provided by SkipVought, and modify it for the month we require

If

DateSerial(Year(Date),Month(Date),0)

gives us the last day of previous month, then

DateSerial(Year(Date),Month(Date) + 1,0)

gives us the last day of this month