I have four textboxes on a form for a user to enter four dates. The four
dates a
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


--
DavidW
------------------------------------------------------------------------
DavidW's Profile: http://www.excelforum.com/member.php...o&userid=32630
View this thread: http://www.excelforum.com/showthread...hreadid=527350

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
--
- K Dales


"DavidW" wrote:


I have four textboxes on a form for a user to enter four dates. The four
dates a
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 todays 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. (Im 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.

Ive 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


--
DavidW
------------------------------------------------------------------------
DavidW's Profile: http://www.excelforum.com/member.php...o&userid=32630
View this thread: http://www.excelforum.com/showthread...hreadid=527350