NETWORKDAYS alternative, for use without Analysis ToolPak

O

Olly

I'm trying to build a formula to calculate the number of working days /
hours between 2 specified dates. This will be used in multiple workbooks, by
various users, who may not have the ability to run code or install the
Analysis toolpak.

I've consulted http://www.cpearson.com/excel/DateTimeWS.htm and modified the
formula accordingly - so far so good! But this still relies on the
NETWORKDAYS function from the Analysis ToolPak. How can I rewrite this
formula to avoid the networkdays function?
 
V

vezerid

How about...

=A2-A1+SUMPRODUCT(--(((WEEKDAY(ROW(INDIRECT(A1&":"&A2)),
2)>5)+ISNUMBER(MATCH(ROW(INDIRECT(A1&":"&A2)),holidays,0)))>0))

HTH
Kostis Vezerides
 
O

Olly

I'm confused now. That formula gives me a number of days between 2 dates, as
long as there is no time entered.

So I replaced A1 with INT(A1), A2 with INT(A2) - and it almost gives me what
I want, overall. But it doesn't take the available hours of a working day
into account.

Anyway, moving on...

The formula I have so far is:

=IF(AND(INT(StartDateTime)=INT(EndDateTime),NOT(ISNA(MATCH(INT(StartDateTime),PublicHolidays,0)))),0,IF(INT(StartDateTime)=INT(EndDateTime),((EndDateTime-StartDateTime))/(WDEnd-WDStart),MAX(networkdays(StartDateTime+1,EndDateTime-1,PublicHolidays),0)+INT(24*(((MOD(EndDateTime,1))-(MOD(StartDateTime,1)))+(WDEnd-WDStart))/(24*(WDEnd-WDStart)))+(MOD((MOD(EndDateTime,1)-WDStart)+(WDEnd-(MOD(StartDateTime,1))),(WDEnd-WDStart)))/(WDEnd-WDStart)))

StartDateTime is a cell containing the start date/time value.
StartDateTime is a cell containing the end date/time value.
WDStart is a cell containing the start time of a working day
WDEnd is a cell containing the end time of a working day
PublicHolidays is a named range containing the dates of holidays (to be
excluded from count)

This works, perfectly - gives me exactly what I want as an answer. What I am
trying to do is replace the NETWORKDAYS function in the above formula, so I
can distribute the formula to users who cannot use the Analysis ToolPak.
 
V

vezerid

Olly,
NETWORKDAYS, as far as I know, does not calculate time, only days. The
formula I gave you could replace the call to networkdays in your
formula.

Does this help?
Kostis
 
O

Olly

No. Your formula does not give the exact same results as the networkdays
function...

vezerid said:
Olly,
NETWORKDAYS, as far as I know, does not calculate time, only days. The
formula I gave you could replace the call to networkdays in your
formula.

Does this help?
Kostis

I'm confused now. That formula gives me a number of days between 2 dates,
as
long as there is no time entered.

So I replaced A1 with INT(A1), A2 with INT(A2) - and it almost gives me
what
I want, overall. But it doesn't take the available hours of a working day
into account.

Anyway, moving on...

The formula I have so far is:

=IF(AND(INT(StartDateTime)=INT(EndDateTime),NOT(ISNA(MATCH(INT(StartDateTime),PublicHolidays,0)))),0,IF(INT(StartDateTime)=INT(EndDateTime),((EndDateTime-StartDateTime))/(WDEnd-WDStart),MAX(networkdays(StartDateTime+1,EndDateTime-1,PublicHolidays),0)+INT(24*(((MOD(EndDateTime,1))-(MOD(StartDateTime,1)))+(WDEnd-WDStart))/(24*(WDEnd-WDStart)))+(MOD((MOD(EndDateTime,1)-WDStart)+(WDEnd-(MOD(StartDateTime,1))),(WDEnd-WDStart)))/(WDEnd-WDStart)))

StartDateTime is a cell containing the start date/time value.
StartDateTime is a cell containing the end date/time value.
WDStart is a cell containing the start time of a working day
WDEnd is a cell containing the end time of a working day
PublicHolidays is a named range containing the dates of holidays (to be
excluded from count)

This works, perfectly - gives me exactly what I want as an answer. What I
am
trying to do is replace the NETWORKDAYS function in the above formula, so
I
can distribute the formula to users who cannot use the Analysis ToolPak.

--
Olly


How about...

HTH
Kostis Vezerides
I'm trying to build a formula to calculate the number of working days
/
hours between 2 specified dates. This will be used in multiple
workbooks,
by
various users, who may not have the ability to run code or install the
Analysis toolpak.
I've consultedhttp://www.cpearson.com/excel/DateTimeWS.htmandmodified
the
formula accordingly - so far so good! But this still relies on the
NETWORKDAYS function from the Analysis ToolPak. How can I rewrite this
formula to avoid the networkdays function?
 
D

daddylonglegs

Hello Olly,

Can you confirm exactly what your requirements are:

Do you need to exclude holidays?

Are your start and end times/dates always within business hours or might
they be outside these (i.e. at evenings, weekends, holidays)?

This formula will calculate business hours between your start and end
times/dates

=(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(StartDateTime)&":"&INT(EndDateTime))),2)<6),--ISNA(MATCH(ROW(INDIRECT(INT(StartDateTime)&":"&INT(EndDateTime))),PublicHolidays,0)))-1)*(WDEnd-WDStart)+MOD(EndDateTime,1)-MOD(StartDateTime,1)

notes:

Result cell should be formatted as [h]:mm. If you want the result in decimal
number of hours, e.g. 64.5 rather than 64:30 then multiply entire formula by
24 and format result cell as number

StartDateTime and EndDateTime should be within business hours [if you can't
guarantee that then this can still be done but you need a more complex
formula]

PublicHolidays should be a single column or single row range
 
D

daddylonglegs

This formula will work when StartDateTime and EndDateTime are any time, even
at weekend, evenings etc.


=(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(StartDateTime)&":"&INT(EndDateTime))),2)<6),--ISNA(MATCH(ROW(INDIRECT(INT(StartDateTime)&":"&INT(EndDateTime))),PublicHolidays,0)))-1)*(WDEnd-WDStart)+IF(AND(WEEKDAY(EndDateTime,2)<6,ISNA(MATCH(INT(EndDateTime),PublicHolidays,0))),MEDIAN(MOD(EndDateTime,1),WDEnd,WDStart),WDEnd)-MEDIAN(AND(WEEKDAY(StartDateTime,2)<6,ISNA(MATCH(INT(StartDateTime),PublicHolidays,0)))*MOD(StartDateTime,1),WDEnd,WDStart)

daddylonglegs said:
Hello Olly,

Can you confirm exactly what your requirements are:

Do you need to exclude holidays?

Are your start and end times/dates always within business hours or might
they be outside these (i.e. at evenings, weekends, holidays)?

This formula will calculate business hours between your start and end
times/dates

=(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(StartDateTime)&":"&INT(EndDateTime))),2)<6),--ISNA(MATCH(ROW(INDIRECT(INT(StartDateTime)&":"&INT(EndDateTime))),PublicHolidays,0)))-1)*(WDEnd-WDStart)+MOD(EndDateTime,1)-MOD(StartDateTime,1)

notes:

Result cell should be formatted as [h]:mm. If you want the result in decimal
number of hours, e.g. 64.5 rather than 64:30 then multiply entire formula by
24 and format result cell as number

StartDateTime and EndDateTime should be within business hours [if you can't
guarantee that then this can still be done but you need a more complex
formula]

PublicHolidays should be a single column or single row range
 
O

Olly

Brilliant! Many, many thanks.

--
Olly

daddylonglegs said:
This formula will work when StartDateTime and EndDateTime are any time,
even
at weekend, evenings etc.


=(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(StartDateTime)&":"&INT(EndDateTime))),2)<6),--ISNA(MATCH(ROW(INDIRECT(INT(StartDateTime)&":"&INT(EndDateTime))),PublicHolidays,0)))-1)*(WDEnd-WDStart)+IF(AND(WEEKDAY(EndDateTime,2)<6,ISNA(MATCH(INT(EndDateTime),PublicHolidays,0))),MEDIAN(MOD(EndDateTime,1),WDEnd,WDStart),WDEnd)-MEDIAN(AND(WEEKDAY(StartDateTime,2)<6,ISNA(MATCH(INT(StartDateTime),PublicHolidays,0)))*MOD(StartDateTime,1),WDEnd,WDStart)

daddylonglegs said:
Hello Olly,

Can you confirm exactly what your requirements are:

Do you need to exclude holidays?

Are your start and end times/dates always within business hours or might
they be outside these (i.e. at evenings, weekends, holidays)?

This formula will calculate business hours between your start and end
times/dates

=(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(StartDateTime)&":"&INT(EndDateTime))),2)<6),--ISNA(MATCH(ROW(INDIRECT(INT(StartDateTime)&":"&INT(EndDateTime))),PublicHolidays,0)))-1)*(WDEnd-WDStart)+MOD(EndDateTime,1)-MOD(StartDateTime,1)

notes:

Result cell should be formatted as [h]:mm. If you want the result in
decimal
number of hours, e.g. 64.5 rather than 64:30 then multiply entire formula
by
24 and format result cell as number

StartDateTime and EndDateTime should be within business hours [if you
can't
guarantee that then this can still be done but you need a more complex
formula]

PublicHolidays should be a single column or single row range

Olly said:
No. Your formula does not give the exact same results as the
networkdays
function...

Olly,
NETWORKDAYS, as far as I know, does not calculate time, only days.
The
formula I gave you could replace the call to networkdays in your
formula.

Does this help?
Kostis

I'm confused now. That formula gives me a number of days between 2
dates,
as
long as there is no time entered.

So I replaced A1 with INT(A1), A2 with INT(A2) - and it almost gives
me
what
I want, overall. But it doesn't take the available hours of a
working day
into account.

Anyway, moving on...

The formula I have so far is:

=IF(AND(INT(StartDateTime)=INT(EndDateTime),NOT(ISNA(MATCH(INT(StartDateTime),PublicHolidays,0)))),0,IF(INT(StartDateTime)=INT(EndDateTime),((EndDateTime-StartDateTime))/(WDEnd-WDStart),MAX(networkdays(StartDateTime+1,EndDateTime-1,PublicHolidays),0)+INT(24*(((MOD(EndDateTime,1))-(MOD(StartDateTime,1)))+(WDEnd-WDStart))/(24*(WDEnd-WDStart)))+(MOD((MOD(EndDateTime,1)-WDStart)+(WDEnd-(MOD(StartDateTime,1))),(WDEnd-WDStart)))/(WDEnd-WDStart)))

StartDateTime is a cell containing the start date/time value.
StartDateTime is a cell containing the end date/time value.
WDStart is a cell containing the start time of a working day
WDEnd is a cell containing the end time of a working day
PublicHolidays is a named range containing the dates of holidays (to
be
excluded from count)

This works, perfectly - gives me exactly what I want as an answer.
What I
am
trying to do is replace the NETWORKDAYS function in the above
formula, so
I
can distribute the formula to users who cannot use the Analysis
ToolPak.

--
Olly



How about...

=A2-A1+SUMPRODUCT(--(((WEEKDAY(ROW(INDIRECT(A1&":"&A2)),
2)>5)+ISNUMBER(MATCH(ROW(INDIRECT(A1&":"&A2)),holidays,0)))>0))

HTH
Kostis Vezerides

I'm trying to build a formula to calculate the number of working
days
/
hours between 2 specified dates. This will be used in multiple
workbooks,
by
various users, who may not have the ability to run code or
install the
Analysis toolpak.

I've
consultedhttp://www.cpearson.com/excel/DateTimeWS.htmandmodified
the
formula accordingly - so far so good! But this still relies on
the
NETWORKDAYS function from the Analysis ToolPak. How can I rewrite
this
formula to avoid the networkdays function?
 
D

DaniMacK

I'm getting a #NAME? error when I use this formula. I'm entering"

=(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(O222)&":"&INT(AE222))),2)<6),--ISNA(MATCH(ROW(INDIRECT(INT(O222)&":"&INT(AE222))),PublicHolidays,0)))-1)*(WDEnd-WDStart)+IF(AND(WEEKDAY(AE222,2)<6,ISNA(MATCH(INT(AE222),PublicHolidays,0))),MEDIAN(MOD(AE222,1),WDEnd,WDStart),WDEnd)-MEDIAN(AND(WEEKDAY(O222,2)<6,ISNA(MATCH(INT(O222),PublicHolidays,0)))*MOD(O222,1),WDEnd,WDStart)

Should I be inserting the start or end date where you show WDEnd and WDStart
as well? I did try that and it didn't seem to help. Thanks in advance for
your assistance.

daddylonglegs said:
This formula will work when StartDateTime and EndDateTime are any time, even
at weekend, evenings etc.


=(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(StartDateTime)&":"&INT(EndDateTime))),2)<6),--ISNA(MATCH(ROW(INDIRECT(INT(StartDateTime)&":"&INT(EndDateTime))),PublicHolidays,0)))-1)*(WDEnd-WDStart)+IF(AND(WEEKDAY(EndDateTime,2)<6,ISNA(MATCH(INT(EndDateTime),PublicHolidays,0))),MEDIAN(MOD(EndDateTime,1),WDEnd,WDStart),WDEnd)-MEDIAN(AND(WEEKDAY(StartDateTime,2)<6,ISNA(MATCH(INT(StartDateTime),PublicHolidays,0)))*MOD(StartDateTime,1),WDEnd,WDStart)

daddylonglegs said:
Hello Olly,

Can you confirm exactly what your requirements are:

Do you need to exclude holidays?

Are your start and end times/dates always within business hours or might
they be outside these (i.e. at evenings, weekends, holidays)?

This formula will calculate business hours between your start and end
times/dates

=(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(StartDateTime)&":"&INT(EndDateTime))),2)<6),--ISNA(MATCH(ROW(INDIRECT(INT(StartDateTime)&":"&INT(EndDateTime))),PublicHolidays,0)))-1)*(WDEnd-WDStart)+MOD(EndDateTime,1)-MOD(StartDateTime,1)

notes:

Result cell should be formatted as [h]:mm. If you want the result in decimal
number of hours, e.g. 64.5 rather than 64:30 then multiply entire formula by
24 and format result cell as number

StartDateTime and EndDateTime should be within business hours [if you can't
guarantee that then this can still be done but you need a more complex
formula]

PublicHolidays should be a single column or single row range

Olly said:
No. Your formula does not give the exact same results as the networkdays
function...

Olly,
NETWORKDAYS, as far as I know, does not calculate time, only days. The
formula I gave you could replace the call to networkdays in your
formula.

Does this help?
Kostis

I'm confused now. That formula gives me a number of days between 2 dates,
as
long as there is no time entered.

So I replaced A1 with INT(A1), A2 with INT(A2) - and it almost gives me
what
I want, overall. But it doesn't take the available hours of a working day
into account.

Anyway, moving on...

The formula I have so far is:

=IF(AND(INT(StartDateTime)=INT(EndDateTime),NOT(ISNA(MATCH(INT(StartDateTime),PublicHolidays,0)))),0,IF(INT(StartDateTime)=INT(EndDateTime),((EndDateTime-StartDateTime))/(WDEnd-WDStart),MAX(networkdays(StartDateTime+1,EndDateTime-1,PublicHolidays),0)+INT(24*(((MOD(EndDateTime,1))-(MOD(StartDateTime,1)))+(WDEnd-WDStart))/(24*(WDEnd-WDStart)))+(MOD((MOD(EndDateTime,1)-WDStart)+(WDEnd-(MOD(StartDateTime,1))),(WDEnd-WDStart)))/(WDEnd-WDStart)))

StartDateTime is a cell containing the start date/time value.
StartDateTime is a cell containing the end date/time value.
WDStart is a cell containing the start time of a working day
WDEnd is a cell containing the end time of a working day
PublicHolidays is a named range containing the dates of holidays (to be
excluded from count)

This works, perfectly - gives me exactly what I want as an answer. What I
am
trying to do is replace the NETWORKDAYS function in the above formula, so
I
can distribute the formula to users who cannot use the Analysis ToolPak.

--
Olly



How about...

=A2-A1+SUMPRODUCT(--(((WEEKDAY(ROW(INDIRECT(A1&":"&A2)),
2)>5)+ISNUMBER(MATCH(ROW(INDIRECT(A1&":"&A2)),holidays,0)))>0))

HTH
Kostis Vezerides

I'm trying to build a formula to calculate the number of working days
/
hours between 2 specified dates. This will be used in multiple
workbooks,
by
various users, who may not have the ability to run code or install the
Analysis toolpak.

I've consultedhttp://www.cpearson.com/excel/DateTimeWS.htmandmodified
the
formula accordingly - so far so good! But this still relies on the
NETWORKDAYS function from the Analysis ToolPak. How can I rewrite this
formula to avoid the networkdays function?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top