Date formula: Month +3, Day -1

lhidden

Hello Team, I am trying to write a formula that takes an inputted date and outputs a M+3, D-1 formula. The very tricky part is input for 1st of month should then be M+2 / D is last day of the month

 

Examples:

 

1. Input 6/5 -> output 9/4  
2. Input 5/25, -> output 8/24 
3. Input 3/31 -> output 6/30    
4. Input 1/1 -> output 3/31     
5. Input 7/1 -> output 9/30      

 

Is this possible? 

JaredDolich

@lhidden 

My Pleasure. In this case you will need to create two modules.

• A monthly system module so we can calculate our monthly values in one place
• A daily system module so we can calculate the new date

In the Monthly System Module  - Here's the blueprint and grid. We will need both Month+2 and Month+3 for when the date is the first of the month. You will also need to create a time range that is one year past what you will need at the daily level.

 

 

Your Daily Module will lookup the monthly values and bring them into the equation. Use your normal model calendar. Note: You'll need to account for the year change and I added the line items in the monthly system module that you'll need. I'll leave it as a puzzle for you to solve in your daily module.

Results match your test script.