PDA

View Full Version : [SOLVED] Date Calculations



mpewsey
10-04-2005, 06:06 AM
I've got a user form which contains a start date for a report formatted as mmm-yyyy.

:help I need to add 3 months to this date to give me an end date for the report.

I'm sure that there must be a simple way to do this but I've been pulling my hair out for the last couple of hours trying to work it out without joy. The female members of staff in my office are also getting rather upset with the language coming from my corner.

Can any of you help me before I have to go out & buy myself a wig/find another job!

Thanks

Martin

MWE
10-04-2005, 06:18 AM
I've got a user form which contains a start date for a report formatted as mmm-yyyy.

:help I need to add 3 months to this date to give me an end date for the report.

I'm sure that there must be a simple way to do this but I've been pulling my hair out for the last couple of hours trying to work it out without joy. The female members of staff in my office are also getting rather upset with the language coming from my corner.

Can any of you help me before I have to go out & buy myself a wig/find another job!

Thanks

Martin
Martin: userform text boxes store items as text. To get the end date, you need to:
1. convert the text to a date variable
2. add 3 months
3. convert back
VBA has several nice Date procedures you should explore:
The Month procedure will return the month number for a Date variable
The Year procedure will return a year number for a Date variable

So, if you convert the text into a date variable, say StartDate, and then extract the month and year:
MonthNum = Month(StartDate)
YearNum = Year(StartDate)
then you should be able to add 3 to MonthNum, test if MonthNum is > than 12, etc.

Enough hints?

chocobochick
10-04-2005, 06:21 AM
Given the variable StartDate as a reference to the date provided on the form, you could use a formula like:


=DATE(YEAR(StartDate),MONTH(StartDate)+3,DAY(StartDate))

And try to watch your language in front of the ladies. :thumb

mpewsey
10-04-2005, 06:30 AM
D'oh - I completely forgot about the text boxes (I ought to know better as I've been caught out by it before a couple of years ago).

Many thanks for saving me a lot of grief (just got to go out now to buy some cakes for my female colleagues!)

Martin