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)

Accepted answers

All comments

PrevContributor

Use "start" and "days" functions to fix number of days and then prorata

peter_mcanena

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.

njduncanson

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.

Egor

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.

Screen_Shot_2017-05-25_at_15_7951.png

Quick Links

Can you help?

Unanswered questions

Update email id
Can anyone please explain how to update my registered email ID jn Anaplan.
Predefaulting A Feild in ANAPLAN
Hello Anaplan Community, I have a special requirement from a customer and would appreciate your input. Let me explain the situation: We have a hierarchy with three levels: Level 1, Level 2, and Level 3. Screen 1: Creation of Level 1 Items On this screen, the user selects a team. Based on their selection, an action runs to…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…