Forcing the due date to be a Friday

[jazzista]

Hello: I have a list of invoices with due dates per the attached list. How can I create a formula where I am forcing the due to be a Friday? I have tried using =IF(WEEKDAY(I5,1)<>6,WEEKDAY(i5,1)-6,I5) but I am missing the original due date and something else and of course, the formula does not work .I want to create a new column called Friday Due date with the new dates . Can somebody help please? Thanks in advance

Due date	Weekday nunb	Weekday
22-Jan-17	1	Sunday
14-Feb-17	3	Tuesday
25-Jan-17	4	Wednesday
13-Mar-17	2	Monday
20-Jan-17	6	Friday
27-Feb-17	2	Monday
30-Mar-17	5	Thursday
20-Mar-17	2	Monday
30-Jan-17	2	Monday
13-Feb-17	2	Monday
6-Jan-17	6	Friday
7-Jan-17	7	Saturday
19-Feb-17	1	Sunday
15-Feb-17	4	Wednesday

<tbody>
</tbody>

 

[Jonmo1]

Are you saying the due date should be the NEXT friday, or Previous Friday ?
Which version of Excel ?

If you have XL2010+, this will return the following Friday from a given date (or remain the same for date that is already friday)
=WORKDAY.INTL(A2-1,1,"1111011")

 

[SpillerBD]

to push to the next Friday, an option would be

=D7+(7-WEEKDAY(D7,16))

 

[jazzista]

Hi Jonmo1: I have excel 2010. This formula worked nicely and yes I needed for the following Friday: This is what I needed. How would you do it if you wanted to retrieve the prior Friday? Thanks for the help. Regards

Are you saying the due date should be the NEXT friday, or Previous Friday ?
Which version of Excel ?

If you have XL2010+, this will return the following Friday from a given date (or remain the same for date that is already friday)
=WORKDAY.INTL(A2-1,1,"1111011")

 

[jazzista]

Jonmo1: I meant to say that I have excel 2016. Thanks . And very clever and elegant solution Thanks again

 

[Jonmo1]

Reverse the math to get the previous friday

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

 

[jazzista]

Hi SpillerBD: Great solution. Quick question why 16 after D7). Thanks

 

[SpillerBD]

Two weeks +1 makes next week, but also need +1 for the Saturday, makes 16. Then we back up a hair.