Excel - enter the start date of the week as a formula

Question

I have this function saved as a macro:

Function getWeekdayStartDate(ActualDate As Date) As Date
    Dim WeekdayStartDate As Date
    WeekdayStartDate = DateValue(DateAdd("d", -Weekday(ActualDate, vbFriday) + 1, ActualDate))
    getWeekdayStartDate = WeekdayStartDate
End Function

This gives me a date that is the first day of the accountant period. The first day of this week is not Monday, but Friday.

I tried these formulas:

=DATE(2016; 6; 1)-WEEKDAY(DATE(2016; 6; 1)-6)
=DATE(2016;6;24)+1-WEEKDAY(DATE(2016;6;24);15)

to get this date, but no luck.

Any ideas on how to convert this function to an excel formula?

Answer 1

If you just want your formula to get the Friday date closer to the specified date (but always earlier), you almost got it with your second formula, you just need to add 1 to the second part of the formula

=DATE(2016;6;24)+1-WEEKDAY(DATE(2016;6;24)+1;16)

Answer 2

WORKDAY.INTL(Start_date, Days, Optional Weekend, Additional Holidays) WORKDAY (Start_date, Days, Additional Holidays) , , , .

B2

=WORKDAY.INTL(A2,1,"1111011")

A2 , 1 -1 . 1111011 1 , , 0 - - .