Billing by month

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a billing start date that I do the following with in a Text Box.

=(Now()-[OB_Start_Date])/30

I would like to have it so that the answer will always round up to a whole
number. If the billing goes one day into the month the client pay the whole
month rate.
 
I have a billing start date that I do the following with in a Text Box.

=(Now()-[OB_Start_Date])/30

I would like to have it so that the answer will always round up to a whole
number. If the billing goes one day into the month the client pay the whole
month rate.
Click to expand...

Well, not all months are 30 days. What do you mean by "goes one day
into the month"? Is a month 30 days, or are you referring to a
calendar month, or what?

Rounding "up" would mean that any positive value, even one second,
would round up to a month. Is that your intent?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Yes that is what I would like. Also sorry for the 2nd post!! I could not find
it after I posted it so I did it again.

I know that all months are not 30 day but the 30 days will get me in the
ball park.

Thanks John.

John Vinson said:
I have a billing start date that I do the following with in a Text Box.

=(Now()-[OB_Start_Date])/30

I would like to have it so that the answer will always round up to a whole
number. If the billing goes one day into the month the client pay the whole
month rate.
Click to expand...

Well, not all months are 30 days. What do you mean by "goes one day
into the month"? Is a month 30 days, or are you referring to a
calendar month, or what?

Rounding "up" would mean that any positive value, even one second,
would round up to a month. Is that your intent?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
Yes that is what I would like. Also sorry for the 2nd post!! I could not find
it after I posted it so I did it again.

I know that all months are not 30 day but the 30 days will get me in the
ball park.
Click to expand...

Ok... try
= Fix((Date()-[OB_Start_Date])/30 + 1)
Click to expand...

If the OB_StartDate is 15 days ago, the subtraction will yield 15;
dividing by 30 will give .5; adding 1 will give 1.5; and Fix() will
roud down to 1.0.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Try one of the following:

DateDiff("m", OBStartDate, OBCurrDate) + ((Day(OBStartDate) <=
Day(OBCurrDate)) * -1)

Sample Results
01 May 2004 to 15 Jun 2004 = 2 (01 May to 31 May, 1 Jun to 15 Jun)
16 May 2004 to 15 Jun 2004 = 1 (16 May to 15 Jul)
31 May 2004 to 01 Jun 2004 = 1
16 May 2004 to 16 Jun 2004 = 2 (16 May to 15 Jul, 16 Jun)
31 May 2004 to 01 Jul 2004 = 2 (31 May to 30 Jun, 1 Jul)

DateDiff("m", OBStartDate, OBStopDate) +1

Sample Results
01 May 2004 to 15 Jun 2004 = 2 (May, Jun)
16 May 2004 to 15 Jun 2004 = 2 (May, Jun)
31 May 2004 to 01 Jun 2004 = 2 (May, Jun)
16 May 2004 to 16 Jun 2004 = 2 (May, Jun)
31 May 2004 to 01 Jul 2004 = 3 (May, Jun, Jul)

Brian
 
Back
Top