# 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
• Question
• Updated 3 years ago

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

• 0 Points

Posted 3 years ago

• 0

Xavier Fan, Champion

• 650 Points
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.