excel if date falls on weekend default to previous friday

0
votes

I have the following two functions in different columns and I need to apply a function to both :

=DATE(YEAR(E5);MONTH(E5);DAY(E5))

=TRUNC([@CAT]-TIME(10;0;0))

For both of them I need to default to a Friday if the date falls over the weekend. if date date is during the week it needs to keep the weekday date.

e.g: Sunday 2018/03/11 needs to be 2018/03/09

e.g: Monday 2018/03/5 needs to stay the same 2018/03/05

I have tried using an IF statement with a Weekday (1-7) but when its false it minus a day or two from the date (So on a Thursday it goes to Tuesday)

2
excelexcel-formula
You should look into WORKDAY – cybernetic.nomad

2 Answers

1
votes
=WORKDAY(your_formula +1;-1)

will do what you want.

If your_formula resolves to a Sat or Sun, and we add one(1) day, then subtract one (1) workday, the preceding Fri will be the result, Since either Sun or Mon minus one workday --> Friday.

0
votes

To literally get what you're after, I used this formula:

=IF(WEEKDAY(A2)=1,A2-2,IF(WEEKDAY(A2)=7,A2-1,A2))

On my machine Sunday is 1, and Saturday is 7.

Demo Sheet