Calculating dates based on the current date

D

DavidW

I have four textboxes on a form for a user to enter four dates. The four
dates are:
1.) Beginning date of the current period.
2.) Ending date of the current period.
3.) Beginning date of the previous period.
4.) Ending date of the previous period.

I would like for the default text of the first textbox to be the first
day of the previous month and the second textbox to be the last day of
the previous month.
The third textbox will have the first day of the previous month and the
year will be the previous year. The fourth textbox will have the last
day of the previous month and the year will be the previous year.

IE: If today’s date is 3/29/2006 then the first textbox will have
2/1/2006, the second will have 2/28/2006, the third will have 2/1/2005,
and the fourth will have 2/28/2005. (I’m ignoring leap years for now)

This will all be done with VBA, probably in “Private Sub
UserForm_Initialize()”

I am able to get the previous month with this code:
StartDate = DateAdd("m", -1, HoldDate) ‘ HoldDate = current month

I also know how to get the last day of the previous month with:
iDaysInMonth = Day(DateAdd("d", -1, DateSerial _
(Year(StartDate), Month(StartDate) + 1, 1)))

iDaysInMonth will now be 28.

What would be the easiest way to take that information and get
variables that hold the dates 2/1/2006, 2/28/2006, 2/1/2005, and
2/28/2005.

I’ve found that some of the code I would have used with VB.Net is not
working with VBA in Excel. Otherwise, I would have it solved.

Are there any suggestions on the best way to approach this using VBA?

Thanks
 
G

Guest

I think you can make it a little more straightforward with the DateSerial
function.
First day of prior month:
=DateSerial(Year(Date),Month(Date)-1,1)
Last day of prior month:
=DateSerial(Year(Date),Month(Date),1)-1
For last year:
=DateSerial(Year(Date)-1,Month(Date)-1,1)
=DateSerial(Year(Date)-1,Month(Date),1)-1
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top