Calculate End Date (excluding weekends and holidays)

guitaronmyback · External Since: May 06, 2009 Posts: 3

Hello,

I have a start days and a number (representing days for a given project). I
want to calculate the end date and exclude weekends and certain holidays.

Is this possible?

Thank you

Mike H · External Since: May 24, 2006 Posts: 1059

Hi,

Try this

=WORKDAY(B1,25,Holidays)

Where

B1=start date
25 is the days for the project
Holidays is a named range containing holoiday dates.

Mike

"guitaronmyback" wrote:

> Hello,
>
> I have a start days and a number (representing days for a given project). I
> want to calculate the end date and exclude weekends and certain holidays.
>
> Is this possible?
>
> Thank you

guitaronmyback · External Since: May 06, 2009 Posts: 3

Thank you for the tip, but I got the #name? as a result

"Mike H" wrote:

> Hi,
>
> Try this
>
> =WORKDAY(B1,25,Holidays)
>
> Where
>
> B1=start date
> 25 is the days for the project
> Holidays is a named range containing holoiday dates.
>
> Mike
>
> "guitaronmyback" wrote:
>
> > Hello,
> >
> > I have a start days and a number (representing days for a given project). I
> > want to calculate the end date and exclude weekends and certain holidays.
> >
> > Is this possible?
> >
> > Thank you

Domenic · External Since: Mar 28, 2009 Posts: 21

In article <CB556204-9D06-40F5-AC1D-C73F99349106.RemoveThis@microsoft.com>,
guitaronmyback <guitaronmyback.RemoveThis@discussions.microsoft.com> wrote:

> Hello,
>
> I have a start days and a number (representing days for a given project). I
> want to calculate the end date and exclude weekends and certain holidays.
>
> Is this possible?
>
> Thank you

Have a look at the WORKDAY function...

--
Domenic
http://www.xl-central.com

Rick Rothstein · External Since: Jan 15, 2009 Posts: 75

The WORKDAY function requires the Analysis ToolPak add-in to be loaded.
Click Tools/Add-Ins from Excel's menu bar and put a check mark next to the
Analysis ToolPak item.

--
Rick (MVP - Excel)

"guitaronmyback" <guitaronmyback.DeleteThis@discussions.microsoft.com> wrote in message
news:448B2E92-2562-4138-A25B-F445B65B5A36@microsoft.com...
> Thank you for the tip, but I got the #name? as a result
>
>
> "Mike H" wrote:
>
>> Hi,
>>
>> Try this
>>
>> =WORKDAY(B1,25,Holidays)
>>
>> Where
>>
>> B1=start date
>> 25 is the days for the project
>> Holidays is a named range containing holoiday dates.
>>
>> Mike
>>
>> "guitaronmyback" wrote:
>>
>> > Hello,
>> >
>> > I have a start days and a number (representing days for a given
>> > project). I
>> > want to calculate the end date and exclude weekends and certain
>> > holidays.
>> >
>> > Is this possible?
>> >
>> > Thank you

guitaronmyback · External Since: May 06, 2009 Posts: 3

It worked, but it seemed to add an extra day....10 workdays, starts on a
Monday and the final date was a Monday, when it should be a Friday....any way
of fixing that?

"Rick Rothstein" wrote:

> The WORKDAY function requires the Analysis ToolPak add-in to be loaded.
> Click Tools/Add-Ins from Excel's menu bar and put a check mark next to the
> Analysis ToolPak item.
>
> --
> Rick (MVP - Excel)
>
>
> "guitaronmyback" <guitaronmyback RemoveThis @discussions.microsoft.com> wrote in message
> news:448B2E92-2562-4138-A25B-F445B65B5A36@microsoft.com...
> > Thank you for the tip, but I got the #name? as a result
> >
> >
> > "Mike H" wrote:
> >
> >> Hi,
> >>
> >> Try this
> >>
> >> =WORKDAY(B1,25,Holidays)
> >>
> >> Where
> >>
> >> B1=start date
> >> 25 is the days for the project
> >> Holidays is a named range containing holoiday dates.
> >>
> >> Mike
> >>
> >> "guitaronmyback" wrote:
> >>
> >> > Hello,
> >> >
> >> > I have a start days and a number (representing days for a given
> >> > project). I
> >> > want to calculate the end date and exclude weekends and certain
> >> > holidays.
> >> >
> >> > Is this possible?
> >> >
> >> > Thank you
>
>

Rick Rothstein · External Since: Jan 15, 2009 Posts: 75

Subtract 1... the difference is because you are thinking "counting numbers"
as opposed to "subtraction of two numbers"... 1, 2, 3, 4 5 is five counting
numbers but the difference between 1 and 5 is four.

--
Rick (MVP - Excel)

"guitaronmyback" <guitaronmyback DeleteThis @discussions.microsoft.com> wrote in message
news:A0503C66-BFDE-417B-B086-7730B701DFDB@microsoft.com...
> It worked, but it seemed to add an extra day....10 workdays, starts on a
> Monday and the final date was a Monday, when it should be a Friday....any
> way
> of fixing that?
>
> "Rick Rothstein" wrote:
>
>> The WORKDAY function requires the Analysis ToolPak add-in to be loaded.
>> Click Tools/Add-Ins from Excel's menu bar and put a check mark next to
>> the
>> Analysis ToolPak item.
>>
>> --
>> Rick (MVP - Excel)
>>
>>
>> "guitaronmyback" <guitaronmyback DeleteThis @discussions.microsoft.com> wrote in
>> message
>> news:448B2E92-2562-4138-A25B-F445B65B5A36@microsoft.com...
>> > Thank you for the tip, but I got the #name? as a result
>> >
>> >
>> > "Mike H" wrote:
>> >
>> >> Hi,
>> >>
>> >> Try this
>> >>
>> >> =WORKDAY(B1,25,Holidays)
>> >>
>> >> Where
>> >>
>> >> B1=start date
>> >> 25 is the days for the project
>> >> Holidays is a named range containing holoiday dates.
>> >>
>> >> Mike
>> >>
>> >> "guitaronmyback" wrote:
>> >>
>> >> > Hello,
>> >> >
>> >> > I have a start days and a number (representing days for a given
>> >> > project). I
>> >> > want to calculate the end date and exclude weekends and certain
>> >> > holidays.
>> >> >
>> >> > Is this possible?
>> >> >
>> >> > Thank you
>>
>>