Calculate next workday after adding calendar days to date in cell
in [Functions]
|
Prev: Convert text entered as minutes/seconds to minutes
Next: How to freeze one column vertically, one horizontal in same sp
From: Darrell on 5 May 2010 15:43 I want to add 100 calendar days to a date in a cell and when that date falls on a weekend or holiday the formula will return the next workday vs. returning a weekend date. I tried the workday function but it counted 100 workdays not calendar days. Thanks in advance
From: Mike H on 5 May 2010 16:16 Darrell, One way. Holidays is a named range containing your holiday dates =A1+100+CHOOSE(WEEKDAY(A1+100,2),0,0,0,0,0,2,1)+SUMPRODUCT((Holidays>=A1)*(Holidays<=A1+(100+SUMPRODUCT((Holidays>=A1)*(Holidays<=A1+100))))) -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Darrell" wrote: > I want to add 100 calendar days to a date in a cell and when that date falls > on a weekend or holiday the formula will return the next workday vs. > returning a weekend date. > > I tried the workday function but it counted 100 workdays not calendar days. > > Thanks in advance >
From: Steve Dunn on 5 May 2010 16:52 Perhaps, untested: =workday(A1+99,1) "Darrell" <Darrell(a)discussions.microsoft.com> wrote in message news:266BDAC6-F404-410C-9EBC-174C5A94A6FC(a)microsoft.com... >I want to add 100 calendar days to a date in a cell and when that date >falls > on a weekend or holiday the formula will return the next workday vs. > returning a weekend date. > > I tried the workday function but it counted 100 workdays not calendar > days. > > Thanks in advance >
From: Steve Dunn on 5 May 2010 16:58
Missed holidays =workday(A1+99,1,Holidays) "Steve Dunn" <stunn(a)sky.com> wrote in message news:ACE1D29B-4704-470E-A29B-49BAE35CFC3A(a)microsoft.com... > Perhaps, untested: > > =workday(A1+99,1) > > > > > "Darrell" <Darrell(a)discussions.microsoft.com> wrote in message > news:266BDAC6-F404-410C-9EBC-174C5A94A6FC(a)microsoft.com... >>I want to add 100 calendar days to a date in a cell and when that date >>falls >> on a weekend or holiday the formula will return the next workday vs. >> returning a weekend date. >> >> I tried the workday function but it counted 100 workdays not calendar >> days. >> >> Thanks in advance >> > |