Date formula: Month +3, Day -1

lhidden
lhidden
in Modeling

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? 

Tagged:

Best Answer

  • JaredDolich
    JaredDolich
    Answer ✓

    @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.

    MonthPlus3-001.png

     

    MonthPlus3-003.png

     

    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.

    MonthPlus3-002.png

    Results match your test script.

    MonthPlus3-004.png

Answers

  • JaredDolich
    JaredDolich

    @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.

  • lhidden
    lhidden

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

     

    Thank you!

Categories