# Replace Saturday with Friday & Sunday with Monday

#### LIFEcontained

##### New Member
Hi,

I've read several past posts on NETWORKDAY & WEEKDAY functions, but I'm still at a loss for my situation. (My sophistication level is low.)

I have a spreadsheet with an event date...and then several actions based on the event date. I want these actions to return only week days, regardless of what day of the week the event date is set for.

I'm using formulas like these (B4 is the event date):
=IF(ISBLANK(\$B\$4),"",+\$B\$4-14)
=IF(ISBLANK(\$B\$4),"",+\$B\$4+7)
=IF(ISBLANK(\$B\$4),"",+\$B\$4+140)

Is there a way to have the cells that return a Saturday, replace it with a Friday instead? And those that return a Sunday replace it with a Monday? (I dream of having holidays switched out eventually, but I'd be happy with just getting over the weekend hump.)

Jan

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### Lewiy

##### Well-known Member
Try this:
Code:
``=IF(WEEKDAY(A1,2)=6,A1-1,IF(WEEKDAY(A1,2)=7,A1+1,A1))``

#### facethegod

##### Well-known Member
Try this event date in A1...

Code:
``=IF(ISBLANK(A1),"",IF(WEEKDAY(A1-14,2)>5,CHOOSE(WEEKDAY(A1-14,2)-5,A1-15,A1-13),A1-14))``

Code:
``=IF(ISBLANK(A1),"",IF(WEEKDAY(A1+7,2)>5,CHOOSE(WEEKDAY(A1+7,2)-5,A1+6,A1+8),A1-14))``

Code:
``=IF(ISBLANK(A1),"",IF(WEEKDAY(A1-140,2)>5,CHOOSE(WEEKDAY(A1-140,2)-5,A1-141,A1-139),A1-14))``

#### barry houdini

##### MrExcel MVP
If you want to avoid holidays too you could try using WORKDAY function from Analysis ToolPak. If you use this formula then you'll always get the next workday AFTER. To add 140 days

=IF(B4="","",WORKDAY(B4+140-1,1,holidays))

holidays should be a named range containing a list of your holiday dates. Similarly to go back 140 days

=IF(B4="","",WORKDAY(B4-140-1,1,holidays))

If you want Sats to become Fris and Suns to become Mons as you say try

=IF(B4="","",IF(WEEKDAY(B4+140)=7,WORKDAY(B4+140,-1,holidays),WORKDAY(B4+140-1,1,holidays)))

again, replace B4+140 with B4-140 for going back 140 days

#### LIFEcontained

##### New Member
Much Appreciation

Many thanks. With your help I now have formulas like these:

=IF(ISBLANK(\$A\$9),"",IF(WEEKDAY(\$A\$9+7,2)>5,CHOOSE(WEEKDAY(\$A\$9+7,2)-5,\$A\$9+6,\$A\$9+8),\$A\$9+7))

=IF(ISBLANK(\$B\$4),"",IF(WEEKDAY(\$B\$4+42,2)>5,CHOOSE(WEEKDAY(\$B\$4+42,2)-5,\$B\$4+41,\$B\$4+43),\$B\$4+42))

Cheers,
Jan

Replies
2
Views
91
Replies
6
Views
373
Replies
4
Views
770
Replies
4
Views
393
Replies
5
Views
687

1,191,204
Messages
5,985,275
Members
439,953
Latest member
suchitha

### 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