When my start date falls on a weekend, how can I make the following Monday my Day 0? Say start date is Feb 7 (Sat), and I need to add three workdays (Feb 9 Mon considered as Day 0).

Excel returns Feb 11 (Wed) as Day 3 when I need to assume that when start date falls on a weekend, it won't be counted just yet.

e.g.

What I need:

Start Date: Feb 7 or 8 (Sat or Sun) - considered excluded

Day 0: Feb 9 (Mon)

Day 1: Feb 10 (Tue)

Day 2: Feb 11 (Wed)

Day 3: Feb 12 (Thu)What excel gives me:

Start Date: Feb 7 or 8 (Sat or Sun) as Day 0

Day 1: Feb 9 (Mon)

Day 2: Feb 10 (Tue)

Day 3: Feb 11 (Wed)** Note that I can't just add 1 to my formula because it would affect those start dates that falls on a weekday.

✔ Best Answer

Did you see my earlier response? I believe I gave you the generic solution, which returned the Day 0 date of the following Monday when the Start Date fell on a weekend. A slight modification gives you the "2 cell" A1:A2 (Start Date:Deadline) solution you are looking for:

=IF(OR(WEEKDAY(A1)=1,WEEKDAY(A1)=7),WORKDAY(A1,4),WORKDAY(A1,3))

All you need to do is check and see if the start date falls on a weekend

day(with the WEEKDAY function) and then set your WORKDAY function as required.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

re: Note that I can't just add 1 to my formula...Just a posting tip:

Since we can't see your worksheet from where we're sitting, it might help us help you if you post the formula you have tried so we know what we are starting with.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Without knowing what you are starting with as far as a formula, here is one solution that seems to meet your requirements. =IF(OR(WEEKDAY(A1)=1,WEEKDAY(A1)=7),WORKDAY(A1,1),WORKDAY(A1,0))

If you need something you can drag down, try incorporating the ROW() function.

For a Start Date in A1, put this in A2 and drag it down. Modify as required.

=IF(OR(WEEKDAY($A$1)=1,WEEKDAY($A$1)=7),

WORKDAY($A$1,ROW()-1),WORKDAY($A$1,ROW()-2))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Thanks guys! Here's the scenario: * When start date falls on a weekend, consider the next working day as the new start date (without changing the original date)

Cell A1: 2/7/15 (start date - saturday)

Cell A2: should be 2/12/15 (deadline - thursday)Wherein:

Day 0: 2/9/15 (monday- should be considered as the working day after the start date which is a weekend)

Day 1: 2/10/15

Day 2: 2/11/15

Day 3: 2/12/15My formula in A2 goes something like this:

=Workday(A1,3)

However, excel gives me 2/11/15 as the deadline.* The formula works well on start dates that fall on a weekday.

Did you see my earlier response? I believe I gave you the generic solution, which returned the Day 0 date of the following Monday when the Start Date fell on a weekend. A slight modification gives you the "2 cell" A1:A2 (Start Date:Deadline) solution you are looking for:

=IF(OR(WEEKDAY(A1)=1,WEEKDAY(A1)=7),WORKDAY(A1,4),WORKDAY(A1,3))

All you need to do is check and see if the start date falls on a weekend

day(with the WEEKDAY function) and then set your WORKDAY function as required.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History