# Calculating a contract's end date for a partial month term

G

#### Guest

Hi! I'm trying to calculate a contract's effective end date based on the
following provided data:
1) contract's effective starting date (i.e. 10/1/2004),
2) term of the contract (in months)

Currently, if the start date is 10/1/2004, term=12 months, the end date is
9/30/2005. I got that calc working, however, if the term is for partial
months (i.e. only for 2 weeks), how would you calculate the end date?

Thank you!
gg

G

#### Guest

Read help about the function DateAdd, it will answer your question

G

#### Guest

It will work if you convert the partial month to decimal then apply to the
formula.

G

#### Graham Mandeno

You can use the DateAdd function to calculate the end date for both monthly
and weekly terms:

[EndDate] = DateAdd("m", [TermInMonths] , [StartDate]) - 1

or

[EndDate] = DateAdd("ww", [TermInWeeks] , [StartDate]) - 1

G

#### Guest

I'm putting the following calc in the Control Source line under the
Properties. Is this the best place to put the calc?

Enddate=IIf(DateSerial(Year(DateAdd("m",nz([term_mos],0),nz([acceptance_date],0))),Month(DateAdd("m",nz([term_mos],0),nz([acceptance_date],0))),Day(DateAdd("m",nz([term_mos],0),nz([acceptance_date],0)))-1)=DateValue("12/29/1899"),Null,DateSerial(Year(DateAdd("m",nz([term_mos],0),nz([acceptance_date],0))),Month(DateAdd("m",nz([term_mos],0),nz([acceptance_date],0))),Day(DateAdd("m",nz([term_mos],0),nz([acceptance_date],0)))-1))

I'm not sure why it automatically is adding the DateValue("12/29/1899") and
the formula after that point. How do I incorporate the DateAdd using your
suggestion of the DateAdd using the weeks function in this calc?

Thank you for your help!

gg
Graham Mandeno said:
You can use the DateAdd function to calculate the end date for both monthly
and weekly terms:

[EndDate] = DateAdd("m", [TermInMonths] , [StartDate]) - 1

or

[EndDate] = DateAdd("ww", [TermInWeeks] , [StartDate]) - 1

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

gg said:
Hi! I'm trying to calculate a contract's effective end date based on the
following provided data:
1) contract's effective starting date (i.e. 10/1/2004),
2) term of the contract (in months)

Currently, if the start date is 10/1/2004, term=12 months, the end date is
9/30/2005. I got that calc working, however, if the term is for partial
months (i.e. only for 2 weeks), how would you calculate the end date?

Thank you!
gg

G

#### Guest

Thank you!

tynerr said:
It will work if you convert the partial month to decimal then apply to the
formula.

G

#### Graham Mandeno

Wow! That is some expression you have there! It doesn't need to be nearly
so complex.

I suggest you add a field for "TermType". It can be a single byte field - 1
= Monthly, 2 = Weekly, and then on a form you can bind it to an option group
with two radio buttons. Set the default value to whichever is most common.

Then, all you need in the ControlSource for EndDate is:

=DateAdd( Choose( [TermType], "m", "ww" ), [Term], [Acceptance_Date] )
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

gg said:
I'm putting the following calc in the Control Source line under the
Properties. Is this the best place to put the calc?

Enddate=IIf(DateSerial(Year(DateAdd("m",nz([term_mos],0),nz([acceptance_date],0))),Month(DateAdd("m",nz([term_mos],0),nz([acceptance_date],0))),Day(DateAdd("m",nz([term_mos],0),nz([acceptance_date],0)))-1)=DateValue("12/29/1899"),Null,DateSerial(Year(DateAdd("m",nz([term_mos],0),nz([acceptance_date],0))),Month(DateAdd("m",nz([term_mos],0),nz([acceptance_date],0))),Day(DateAdd("m",nz([term_mos],0),nz([acceptance_date],0)))-1))

I'm not sure why it automatically is adding the DateValue("12/29/1899")
and
the formula after that point. How do I incorporate the DateAdd using your
suggestion of the DateAdd using the weeks function in this calc?

Thank you for your help!

gg
Graham Mandeno said:
You can use the DateAdd function to calculate the end date for both
monthly
and weekly terms:

[EndDate] = DateAdd("m", [TermInMonths] , [StartDate]) - 1

or

[EndDate] = DateAdd("ww", [TermInWeeks] , [StartDate]) - 1

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

gg said:
Hi! I'm trying to calculate a contract's effective end date based on
the
following provided data:
1) contract's effective starting date (i.e. 10/1/2004),
2) term of the contract (in months)

Currently, if the start date is 10/1/2004, term=12 months, the end date
is
9/30/2005. I got that calc working, however, if the term is for
partial
months (i.e. only for 2 weeks), how would you calculate the end date?

Thank you!
gg

G

#### Guest

Hi Graham,

Thank you so much for simplifying the solution. I'll give it a try. As you
can tell, I'm just learning VB.

Thanks again!

GG

Graham Mandeno said:
You can use the DateAdd function to calculate the end date for both monthly
and weekly terms:

[EndDate] = DateAdd("m", [TermInMonths] , [StartDate]) - 1

or

[EndDate] = DateAdd("ww", [TermInWeeks] , [StartDate]) - 1

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

gg said:
Hi! I'm trying to calculate a contract's effective end date based on the
following provided data:
1) contract's effective starting date (i.e. 10/1/2004),
2) term of the contract (in months)

Currently, if the start date is 10/1/2004, term=12 months, the end date is
9/30/2005. I got that calc working, however, if the term is for partial
months (i.e. only for 2 weeks), how would you calculate the end date?

Thank you!
gg

## 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.