Google Spreadsheet, finding the previous weekday

2
votes

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)
3
excelgoogle-sheetsgoogle-docs
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 – Siddharth Rout
@Siddarth Rout : lol. fair enough. I added what I tried. and some thoughts as well :) – mdc
I am happy that you took the feedback in a positive manner :) I have retracted my vote and yes... + 1 ;) – Siddharth Rout
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
Actually it does work. I must have mixed up the days somehow. I was almost certain it didnt work, oh well =) – mdc

3 Answers

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

...does the trick.

4
votes

Another option may be:

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

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.