I am trying to do a calculation that seems pretty simple, but can't seem to find the right answer among the many date calculation posts. Hopefully this is not repetitive but here goes:

We need to generate accreditation report data on the length of time students spend in our curriculum. Typically, they enter our academic program in late August or September. As an example let's say 08/24/2018 (called "start_date" in the database; a date field). On the first Friday in August three years later (2021) most students will finish the academic program. Because we need some downstream calculations using this date, I need to get it right. This is primarily because some students take longer and we must report accurately how much longer.

I created a calculation that gets me to the first day of August three years later using 08 as the month 01 as the day and Year (start_date)+3 as the year, but that's not good enough. I need the day to be the first Friday in August of the year 2021 in my example or in general the first Friday in August 3 years after start_date. Any help from you kind folks in the community would be greatly appreciated.

Dave

The key here the "shift" in days:

Let ( [

start = Date ( 8 ; 24 ; 2018 ) ;

end.start = Date ( 8 ; 1 ; year ( start ) + 3 ) ;

day.end = DayOfWeek ( end.start ) ;

shift = 6 - Case ( day.end = 7 ; 0 ; day.end ) ;

result = end.start + shift

];

result

)