Solved How to add workday when start date is weekend?

March 20, 2015 at 11:40:22
Specs: Windows 7
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.


See More: How to add workday when start date is weekend?

Report •

✔ Best Answer
March 24, 2015 at 12:08:29
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



#1
March 20, 2015 at 11:43:14
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


Report •

#2
March 20, 2015 at 11:54:02
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


Report •

#3
March 24, 2015 at 11:00:49
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/15

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


Report •

Related Solutions

#4
March 24, 2015 at 12:08:29
✔ 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


Report •

Ask Question