Google Spreadsheet, finding the previous weekday

Question

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)

You have been with SO for more than `4` years now :) You should know better than to ask a question and expect someone to do the job for you. Sorry but I will have to vote this question to be closed. Questions asking for code must demonstrate a minimal understanding of the problem being solved. Include attempted solutions, why they didn't work, and the expected results. See also: [Stack Overflow question checklist](http://meta.stackexchange.com/questions/156810/stack-overflow-question-checklist) — Siddharth Rout, Oct 13 '13 at 12:58
@Siddarth Rout : lol. fair enough. I added what I tried. and some thoughts as well :) — mdc, Oct 13 '13 at 13:42
I am happy that you took the feedback in a positive manner :) I have retracted my vote and yes... + 1 ;) — Siddharth Rout, Oct 13 '13 at 13:51
This formula looks good to me `=WORKDAY(EDATE(N18, 1)+1, -1)` - can you give an example where you don't get the result you want....and what the required result should be in that case — barry houdini, Oct 13 '13 at 17:44
Actually it does work. I must have mixed up the days somehow. I was almost certain it didnt work, oh well =) — mdc, Oct 14 '13 at 09:23

score 14 · Accepted Answer · answered Oct 14 '13 at 09:26

14

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

...does the trick.

answered Oct 14 '13 at 09:26

mdc

1,161
6
22
37

score 4 · Answer 2 · answered Oct 13 '13 at 14:45

4

Another option may be:

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

answered Oct 13 '13 at 14:45

wchiquito

16,177
2
34
45

score 1 · Answer 3 · answered Oct 13 '13 at 13:35

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.

Google Spreadsheet, finding the previous weekday

3 Answers3

Linked