# Formula to change date to friday if result is weekend

#### mecerrato

##### Board Regular
I have this formula that calculates a date 4 days prior to the entered date in cell H7:
=IF(\$H\$7="","",IFERROR(WORKDAY.INTL(\$H\$7,-4,11,\$C\$18:\$C\$37),""))
It sometimes results in a weekend and I want the formula to give me the Friday before if the result falls on a weekend.

I also have another formula that calculates 4 days after the date entered into H11:
=IF(\$H\$11="","",IFERROR(WORKDAY.INTL(\$H\$11,4,11,\$C\$18:\$C\$37),""))
It sometimes results in a weekend and I want the formula to give me the Monday after if the result falls on a weekend.

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### Fluff

##### MrExcel MVP, Moderator
What do you classify as a weekend?

#### Fluff

##### MrExcel MVP, Moderator
In that case change the 3rd argument in the workday function from 11 to 1

#### JimM

##### Well-known Member

Is the problem because you are using the 11 to define the weekend which is only Sunday, for Saturday & Sunday it should be 1

#### mecerrato

##### Board Regular
Is the problem because you are using the 11 to define the weekend which is only Sunday, for Saturday & Sunday it should be 1
I can't believe how simplet that was and how I missed that, thanks!

#### Fluff

##### MrExcel MVP, Moderator
Glad we could help & thanks for the feedback.

Replies
5
Views
141
Replies
12
Views
1K
Replies
6
Views
304
Replies
3
Views
434
Replies
1
Views
302

1,148,342
Messages
5,746,192
Members
423,997
Latest member
eakenila

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back