Calculations using Dates

Previous Contributor

Calculations using Dates

I need help developing a formula that looks at the start and end dates for an agreement (eg. rent, where rent amount per month is fixed).  The agreement can straddle months or years. I would like to show rent mont by month on a timescale, however, be able to prorate the rental amount in the initial month and the final month where the agreement start and end part-way through the month (eg. Agreement starts 12 August 2016, Ends 22 September 2018 and assume monthly rental of $1000)

Message 1 of 5
4 REPLIES 4
Previous Contributor

RE: Calculations using Dates

Use "start" and "days" functions to fix number of days and then prorata
Message 2 of 5
Regular Contributor

RE: Calculations using Dates

You can prorate the opening month like this:
(END() - Agreement Start + 1) / DAYS()
And prorate the closing month like this:
(Agreement End - START() + 1) / DAYS()
...assuming that Agreement Start and Agreement End are the first and last days of your agreement respectively. These formulae give you a pro-rata multiplier to apply to your rent or whatever.
Message 3 of 5
Highlighted
Occasional Contributor

Re: Calculations using Dates

Did you get a response on this question?  I'm trying to do the same, but don't see how to logically calculate if the month is withing the start-end of the agreement.

Message 4 of 5
Certified Master Anaplanner

Re: Calculations using Dates

Hi,

 

It is actually quite easy to check if a period is within two dates. You will have to convert the dates to periods using PERIOD function. Having done that you can compare periods with < or > operators as you would compare numbers.

 

Ex. IF June 2017 < August 2017 THEN TRUE ELSE FALSE will give us TRUE as a result.

 

See screenshots attached for more details.