Salesforce and other SMB Solutions are coming soon. ×

# Date Calculation

## Recommended Posts

Hi there,

Thanks in advance for any help.

I have an ISP where people pay inadvance for internet access.

Each account has a credit value which is generally 1 month.

Fields are MonthCredit and DaysCredit

e.g. A customers expiration is 1st of february 2004. He comes into the store and pays for 2 months access whcih would advance his expiration date to 1st of April 2004.

I need a script that would look at MonthsCredit and advance the expiration date the value of monthsCredit and move it to the last day of that month.

David gave me this script to find the last day of the month:

Date(Month(Status(CurrentDate))+1, Day(1)-1, Year(Status(CurrentDate)))

Thank you Ron

but they could pay for 3 mths

Ron says:

say they pay for 3 mths I need to advance the date 3 ths tehn get the last day of that

##### Share on other sites

Hi Ron,.

Assuming that you have the monthly rate stored someplace (eg in a global field called gMonthlyRate), the amount of the customer's new payment goes into an amount field called NewAmountPaid and the date they are currently paid up to is in a date field called ExpiryDate, then an example of a script to extend the expiry date would be:

Set Field ["ExpiryDate", "Date(Month(ExpiryDate) + Int(NewAmountPaid / gMonthlyRate), 0, Year(ExpiryDate))"]

Set Field ["TotalPaid", "NewAmountPaid"]

Set Field ["NewAmountPaid", ""]

Assuming that your clients pay in exact multiples of the amount in the gMonthlyRate field and this covers calendar months, that should give you the automatic process you are looking for.

##### Share on other sites

Hi Ron

Can you please be more specific about how you want the date advanced? I am getting confused by apparently conflicting statements. First you said:

"A customers expiration is 1st of february 2004. He comes into the store and pays for 2 months access whcih would advance his expiration date to 1st of April 2004."

That's easy enough for the first of the month. But what would you like the date to be in this case:

"A customers expiration is 31st of January 2004. He comes into the store and pays for 1 months access which would advance his expiration date to ??????????? 2004."

Tha answer could be 29 February or 2 March (effectively 31 February).

But then you said:

"I need a script that would look at MonthsCredit and advance the expiration date the value of monthsCredit and move it to the last day of that month."

So in that case, for an expiration date of 1 February, you want to advance it to 1 March and then to the end of that month - 31 March???? That would be two months credit.

So I am confused. How do you want to advance the date by months? And what about DaysCredit?

##### Share on other sites

Thanks Ray and David,

say a customer joins our service on the 15th of January. There first payment would be prorated to the first of February.

I have worked out the calcs that would total their monthly billing, dependant on what account they joined. Then work out how many days in the month, divide their total montly billing by days in month. THen times it by the amount of days from establishment to the first of the next month. This would give me the cost of that account until the first of the next month, bringing them in line with all accounts which are based on calendar months.

Dear David, this wouldn't be the case:

----------------------------------------

"A customers expiration is 31st of January 2004. He comes into the store and pays for 1 months access which would advance his expiration date to ??????????? 2004."

----------------------------------------------

Because the first month would be prorated..

With this:

------------------------------------------------

So in that case, for an expiration date of 1 February, you want to advance it to 1 March and then to the end of that month - 31 March???? That would be two months credit.

---------------------------------------

My problem is that there are different amounts of days in different months other wise I could advance the date by 30,60 or 90 whatever..

Thank you I hope its alittle clearer.

##### Share on other sites

OK then I think this will do what you want.

You have two fields:

ExpirationDate (date)

MonthsCredit (global number)

On a layout you give the user access to the MonthsCredit field where they enter a whole number. Also give them a button which will run the following script:

If["MonthsCredit"]

Set Field ["ExpirationDate", "Date(Month(ExpirationDate) + MonthsCredit + 1, 1, Year(ExpirationDate)) - 1"]

Set Field ["MonthsCredit", """"]

End If

What the script does is this:

Line1: Checks to see that a value has been entered in MonthsCredit

Line3: Clears the MonthsCredit field (to avoid multiple credits)

Line4: End the If statement

See if that works for you.

• ### Images

• By Soliant Consulting,
• By Soliant Consulting,
• By Soliant Consulting,
• ### Forum Statistics

• Total Topics
33.7k
• Total Posts
141.6k
×
×
• Create New...