I need a formula to calculate a date based on a start date that results in the first or third Friday of the month.

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I need a formula to calculate a date based on a start date that results in the first or third Friday of the month. 


Examples:

Date Entered 05/03/2016 Date Calculated: 05/06/2016

Date Entered 04/30/2016 Date Calculated: 05/06/2016

Date Entered 05/12/2016 Date Calculated: 05/20/2016

Date Entered 05/18/2016 Date Calculated: 05/20/2016

Date Entered 05/23/2016 Date Calculated: 06/03/2016

Photo of STEPHANIE

STEPHANIE

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Photo of Xavier Fan

Xavier Fan, Champion

  • 650 Points 500 badge 2x thumb
Say you have [Date Entered] as your Date field.

Put the following formula into the [Date Calculated] Formula Date field:




var Date FirstDayCurrentMonth = FirstDayOfMonth([Date Entered]);


var Number DayOfWeekFirstDayCurrentMonth = DayOfWeek( $FirstDayCurrentMonth );

var Number DaysToFirstFridayCurrentMonth = Case ($DayOfWeekFirstDayCurrentMonth,

6, 6,

5 - $DayOfWeekFirstDayCurrentMonth);

var Date FirstFridayCurrentMonth = $FirstDayCurrentMonth + Days( $DaysToFirstFridayCurrentMonth );


var Date ThirdFridayCurrentMonth = $FirstFridayCurrentMonth + Days(14);

var Date FirstDayNextMonth = AdjustMonth($FirstDayCurrentMonth, 1);


var Number DayOfWeekFirstDayNextMonth = DayOfWeek( $FirstDayNextMonth );

var Number DaysToFirstFridayNextMonth = Case ($DayOfWeekFirstDayNextMonth,

6, 6,

5 - $DayOfWeekFirstDayNextMonth);

var Date FirstFridayNextMonth = $FirstDayNextMonth + Days( $DaysToFirstFridayNextMonth );


var Date ThirdFridayNextMonth = $FirstFridayNextMonth + Days(14);

If (


[Date Entered] < $FirstFridayCurrentMonth, $FirstFridayCurrentMonth,

[Date Entered] < $ThirdFridayCurrentMonth, $ThirdFridayCurrentMonth,

[Date Entered] < $FirstFridayNextMonth, $FirstFridayNextMonth,

[Date Entered] < $ThirdFridayNextMonth, $ThirdFridayNextMonth

)

====

The basic idea is that you want to calculate when the 1st or 3rd Fridays are in the current month, and the next month (in case your [Date Entered] is after the 3rd Friday of the current month, in which case you need to go to the next month).  

Then compare to see where your [Date Entered] falls within these 4 Fridays, and just go to the next Friday coming up.