NETWORKDAYS alternative, for use without Analysis ToolPak

  • Thread starter Thread starter Olly
  • Start date Start date
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?
 
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 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,
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
 
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
Click to expand...
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.
Click to expand...
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?
Click to expand...
Click to expand...
Click to expand...
 
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
 
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
Click to expand...
 
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?
Click to expand...
Click to expand...
Click to expand...
 
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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top