Replace Saturday with Friday & Sunday with Monday

[LIFEcontained]

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.)

Many thanks in advance for your advice.

Jan

 

[Lewiy]

Try this:

=IF(WEEKDAY(A1,2)=6,A1-1,IF(WEEKDAY(A1,2)=7,A1+1,A1))

 

[facethegod]

Try this event date in A1...

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

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

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

 

[barry houdini]

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]

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