CODEWITHSUNDEEP
excelgoogle-sheetsgoogle-docs
mdc
mdc

Reputation: 1201

Google Spreadsheet, finding the previous weekday

In Google Spreadsheet, or excel if its the same, how do I find the last weekday of day D only if D is not a weekday itself.

I.E:

If D equals weekday = do nothing (D equals D).
If D equals weekend = D equals last weekday.

Edit: i tried this to get the workday one month away:

WORKDAY(EDATE(N18, 1)+1, -1)

But I Was getting some weird results. Perhaps it counts red days as well, but I never specified the region anywhere.

Edit again:

This above example actually works, eg:

=WORKDAY("2013-04-06"+1, -1)

Upvotes: 3

Views: 7629

Answers (3)

mdc
mdc

Reputation: 1201

=WORKDAY("2013-04-06"+1, -1)

...does the trick.

Upvotes: 17

wchiquito
wchiquito

Reputation: 16551

Another option may be:

=IF(WEEKDAY(D1, 2) > 5, D1 - (WEEKDAY(D1, 2) - 5), D1)

Upvotes: 4

Jerry
Jerry

Reputation: 71578

It's not so straightforward, but you can use something like that:

=IF(OR(WEEKDAY(A1)=1,WEEKDAY(A1)=7),A1-CHOOSE(WEEKDAY(A1),2,,,,,,1),A1)

Basically, if the weekday is 1 or 7 (Sunday or Saturday, as this is how Excel treats weekdays), return the date minus 2 if Sunday or minus 1 if Saturday, else the date itself.

Upvotes: 1

Related Questions