Date formula: Month +3, Day -1

lhidden
New Contributor

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

 

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? 

1 ACCEPTED SOLUTION

Accepted Solutions
JaredDolich
Moderator

Re: Date formula: Month +3, Day -1

@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


Jared Dolich

View solution in original post

3 REPLIES 3
JaredDolich
Moderator

Re: Date formula: Month +3, Day -1

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


Jared Dolich
lhidden
New Contributor

Re: Date formula: Month +3, Day -1

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

 

Thank you!

JaredDolich
Moderator

Re: Date formula: Month +3, Day -1

@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


Jared Dolich

View solution in original post