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

  • Thread starter Thread starter Guest
  • Start date Start date
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 :)
 
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
 
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 :)
Click to expand...
Click to expand...
 
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 :)
Click to expand...
Click to expand...
Click to expand...
 
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 :)
Click to expand...
Click to expand...
 
Back
Top