Workdays (continued)

  • Thread starter Thread starter Marcy
  • Start date Start date
M

Marcy

Sorry to beat a dead horse into the ground but here goes:

If cell C12=1/2/2004
And cell B13=44 days (total; not just working days)
How can I return a date in cell C13 that will add C12 +
B13 but have the resultant date return as the next valid
weekday, even if the total days of 44 falls should fall
on a saturday or a sunday or a holiday?

Thanks again!
 
Sorry to beat a dead horse into the ground but here goes:

If cell C12=1/2/2004
And cell B13=44 days (total; not just working days)
How can I return a date in cell C13 that will add C12 +
B13 but have the resultant date return as the next valid
weekday, even if the total days of 44 falls should fall
on a saturday or a sunday or a holiday?

Thanks again!
Click to expand...

This might work:

=workday(C12+B13-1,1)


--ron
 
This is get you to the next valid workday, but holidays are not included.

=IF(WEEKDAY(C12+B13,1)=1,C12+B13+1,IF(WEEKDAY(C12+B13,1)=7,C12+B13+2,C12+B13
))
 
Marcy,

it's better to stay in the same thread. Here is the answer I gave in your
earlier thread

=IF(WEEKDAY(C12+B13,2)>5,C12+B13-WEEKDAY(C12+B13,2)+8,C12+B13)
 
-----Original Message-----
Sorry to beat a dead horse into the ground but here goes:

If cell C12=1/2/2004
And cell B13=44 days (total; not just working days)
How can I return a date in cell C13 that will add C12 +
B13 but have the resultant date return as the next valid
weekday, even if the total days of 44 falls should fall
on a saturday or a sunday or a holiday?

Thanks again!

Try this it seems to work
Click to expand...
=IF(OR(WEEKDAY(c12+b13)=7,WEEKDAY(c12+b13)=1),WORKDAY
(c12+b13,1,Holidays),IF(ISERROR(VLOOKUP
(c12+b13,Holidays,1,FALSE)),c12+b13,WORKDAY
(c12+b13,1,Holidays))).

The flow is a little difficult but that's what happens
when you do this within a cell. Anyway here goes...
if the sum falls on a weekend then compute the next
workday, else
if the sum is not a holiday (and is a workday) then give
me that date, otherwise it is a holiday and compute the
next workday.
 
Well, I tried this and used the following conditions:
C10=12/18/03
B11= 7 days
C11==WORKDAY(C10+B11-1,1)
Unfortunately, the returned date was 12/25/03; which is
clearly a holiday and so not a valid date.

PLEASE tell me how can I re-write this to account for my
defined holiday list? (separate worksheet>>sheet2!B4:B24)

Thanks again people for all the help!
 
Marcy,

You have to supply the list of holidays to the WORKDAY function. E.g.,

=WORKDAY(C10+B11-1,1,Sheet2!B4:B24)

where Sheet2!B2:B24 contains a list of dates to be considered holidays.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
You don't seem to understand the function.. What does the last 1 represent?
You need to either put in the date or a cell with the date

=WORKDAY(C10+B11,-1,"12/25/03")

=WORKDAY(C10+B11,-1,D12)

also why don't you use

=WORKDAY(C10,B11,"12/25/03")

it obviously don't return the same date but if you want
7 workdays after the date in C10 that is how it should be used
 
Hey, Chip--
I think I've run into a problem with this...please note
the following:
C10=12/19/2003
B11=7 days
C11, then should equal 12/19+7, and return 12/30 cause
12/25 is a holiday (so listed in my named range) so it
would advance to the next workday, right?
But, it doesn't..it returns 12/26/03.
Where have I gone wrong?
Here's the formula I"m using:
=WORKDAY(C10+$B11-1,1,HOLIDAYS)
Help...my eyes have crossed and my brain hurts now!
How will I see to stuff the turkey??
Thanks!
 
Marcy.

As I said earlier if you want to add 7 workdays use

=WORKDAY(C10,B11,"12/25/03")

You basically say

=WORKDAY(C10+$B11-1,1,HOLIDAYS)

start date is 12/26/03 (12/19/03 + 7) -1 which is 12/25/03 add one workday
to that
which is 12/26/03

=WORKDAY(C10,B11,"12/25/03")

returns 12/31/03

If you always want -1 use

=WORKDAY(C10,B11-1,"12/25/03")

returns 12/30/03
 
Okay, I see what you're saying now. However, I am copying this formula down a column and the next event may take another 65 days from the result of the previous. Will the -1 always be applicable

That is, C11=12/26 (per your formula)=WORKDAY(C10+$B11-1,1,HOLIDAYS
B12=65 day
B12=your formula=3/1/0
Does this account for holidays (1/1/04, 1/7/04, 2/5/04

What I'm asking is, can this be copied down a colum using the -1 argument of the formula? Or, do I have to enter each cell's formula based on the number of holidays between events?
How can I make this "copy-able"

Thanks for your continued patience with me.
 
Well, I tried this and used the following conditions:
C10=12/18/03
B11= 7 days
C11==WORKDAY(C10+B11-1,1)
Unfortunately, the returned date was 12/25/03; which is
clearly a holiday and so not a valid date.

PLEASE tell me how can I re-write this to account for my
defined holiday list? (separate worksheet>>sheet2!B4:B24)
Click to expand...

=WORKDAY(C10,B11-1,SHEET2!B4:B24) --> Friday, December 26, 2003





--ron
 
=WORKDAY(C10,B11,"12/25/03")

it obviously don't return the same date but if you want
7 workdays after the date in C10 that is how it should be used
Click to expand...

You need to go back and read the original request.

It was to take a DATE, and a number of DAYS, and then return that date so long
as it is not a Sat, Sun or Holiday. If it is, return the next working date
after that date.

So 12/18/03 + 7 --> 12/25/03 which is a holiday. The next Workday is Friday
12/26/03


--ron
 
Although I first read it like that, later posts made me think
it was not.. If so why would she use workday at all?
 
You're absolutely right, Ron. I lost sight of the original question. As I started using the info that you and your colleagues have generously provided, more questions arose. I apologize for "running off the road"

Enjoy your holiday, everyone
Thanks again.
 
Although I first read it like that, later posts made me think
it was not.. If so why would she use workday at all?
Click to expand...

I suggested using WORKDAY because that formula makes it slightly simpler to
incorporate holidays into the algorithm.


--ron
 
Back
Top