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

[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

 

[Guest]

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

 

[Guest]

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

 

[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

 

[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

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

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

 

[Guest]

Thank you!

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

 

[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

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

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

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

 

[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

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

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