Exclude Weekends in Excel

Question

Hi,

I'm new at Excel and am trying to create a work schedule using formulas. For some of the dates I need Saturday to be pushed to Friday and Sunday to be pushed to Monday. For other dates, Saturdays don't need to be excluded but Sundays will still need to be pushed to Monday.

For example, I have an "in home date" (P3) listed, and I want the "drop date"(O3) to be 6 days prior to the "in home date". Right now, I have the drop date formula as =DATE(YEAR(P3), MONTH(P3), DAY(P3)-6). I need to find a way to get this to exclude weekends though.

I've watched a couple of videos explaining how to use the weekdays function, but I can't figure out how to apply it here. I'd really appreciate your help!

Answer 1

Thanks. This could work.

This pushes both Saturday and Sunday to Friday.

I would like to push Sunday to Monday.

Also, I have some rows where I want to leave Saturdays in the schedule and only exclude Sundays. 

Hi,

Try it this way. The -1 is go back 1 day for Saturday and the last 1 is go forward 1 day for Sunday.

=(P3-6)+CHOOSE(WEEKDAY(P3-6,2),0,0,0,0,0,-1,1)

Answer 2

So far this looks like it's working.

Thanks so much for your help!

Answer 3

Use below formula assuming your list of Saturdays are in S1 to S100 (Change $S$1:$S$100 to as per your range)

=IF(WEEKDAY(P3-6)=1,P3-5,IF(WEEKDAY(P3-6)=7,IF(ISNUMBER(MATCH(P3-6,$S$1:$S$100,0)),P3-6,P3-7),P3-6))

Answer 4

Thanks. This could work.

This pushes both Saturday and Sunday to Friday.

I would like to push Sunday to Monday.

Also, I have some rows where I want to leave Saturdays in the schedule and only exclude Sundays.

Answer 5

Hi Katie,

Try this:

Your reference day in P3

=WORKDAY(P3,-6)

and format as date.