Date formula: Month +3, Day -1


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




  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? 

Best Answer

  • JaredDolich


    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.



  • @lhidden 

    Ooooh, I like this question. I think you can do this with the DAYSINMONTH() Function. So add up the days in month for the next three months then subtract 1 day.

    Will that work? I can create an example for you if need.

  • Could you please provide an example? I have never used DAYSINMONTH()


    Thank you!