calculating month to date, year to date, week to date

I've got two cells with a beginning date, and another with an end date.

I'm assigning macros to buttons labelled "WTD", "YTD", "MTD", i.e. week to date, year to date, month to date.

I can work out month to date in formulas pretty easily. It's =DATE(YEAR(today()),MONTH(today))-1,DAY(today))). So cell A1 is today(), A2 is =DATE(YEAR(today()),MONTH(today))-1,DAY(today))

However, I have no idea how to do this in VBA. I've been playing around.

Sub Button4_Click()

ActiveSheet.Range("A1").Value = Date - DateSerial(Year(date), Month(Date)-1, Day(Date))
ActiveSheet.Range("A2").Value = Date

End Sub

This doesn't work the same way as in formulas.

Try the DATEADD() function....

what about calculating of WTD. Is there anyway to just grab business days? E.g. if it's a monday and I click WTD I want to go back to last monday.

So here's what I managed to get. But I can't figure out how to do an actual Month-TO-DATE, or week-TO-DATE.

Please Login or Register  to view this content.

Ideally, If I call WTD function, it should just go to the start of the month. WTD function... should go to the start of the week, e.g. if it's Thursday, it'll go Monday. If it's FRI-SUN it'll go back to monday....
YTD same thing. if it's 06/06/2013, it'll go 01/01/2013....