CODEWITHSUNDEEP
excelexcel-formulavba
westman379
westman379

Reputation: 713

Excel - Get Week Day Start Date as formula

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

It gives me a date which is the first day of the accountant period kinda.
First day of this week is not monday, it's 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 with no luck.

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

Upvotes: 4

Views: 428

Answers (2)

Mark Fitzgerald
Mark Fitzgerald

Reputation: 3068

WORKDAY.INTL(Start_date, Days, Optional Weekend, Optional Holidays) differs from the standard WORKDAY(Start_date, Days, Optional Holidays) in that you can specify different weekend days including a string reflecting a custom work week.

In B2 use

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

Where A2 has the date, 1 returns next Friday and -1 returns last Friday. In the 1111011 string, 1 represents non-workdays starting on Monday while 0 is a workday - in this case Friday.

Upvotes: 2

Jordan
Jordan

Reputation: 4514

If you just want your formula to get the date of the Friday closest to the specified date (but always before) then you nearly had it with your second formula, you just need to add 1 to the second part of the formula too:

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

Upvotes: 4

Related Questions