NETWORKDAYS alternative, for use without Analysis ToolPak
 

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?


--
Olly

NETWORKDAYS alternative, for use without Analysis ToolPak
 

How about...

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

HTH
Kostis Vezerides

On Feb 15, 5:54 pm, "Olly" wrote:
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.htmand 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?

--
Olly

NETWORKDAYS alternative, for use without Analysis ToolPak
 

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(IS NA(MATCH(INT(StartDateTime),PublicHolidays,0)))),0 ,IF(INT(StartDateTime)=INT(EndDateTime),((EndDateT ime-StartDateTime))/(WDEnd-WDStart),MAX(networkdays(StartDateTime+1,EndDateTi me-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

"vezerid" wrote in message
ps.com...
How about...

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

HTH
Kostis Vezerides

On Feb 15, 5:54 pm, "Olly" wrote:
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.htmand 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?

--
Olly

NETWORKDAYS alternative, for use without 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

On Feb 15, 7:50 pm, "Olly" wrote:
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(IS NA(MATCH(INT(StartDateTime),PublicHolidays,0)))),0 ,IF(INT(StartDateTime)=INT(EndDateTime),((EndDateT ime-StartDateTime))/(WDEnd-WDStart),MAX(networkdays(StartDateTime+1,EndDateTi me-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

"vezerid" wrote in message

ps.com...

How about...

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

HTH
Kostis Vezerides

On Feb 15, 5:54 pm, "Olly" wrote:
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?

--
Olly

NETWORKDAYS alternative, for use without Analysis ToolPak
 

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

"vezerid" wrote in message
oups.com...
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

On Feb 15, 7:50 pm, "Olly" wrote:
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(IS NA(MATCH(INT(StartDateTime),PublicHolidays,0)))),0 ,IF(INT(StartDateTime)=INT(EndDateTime),((EndDateT ime-StartDateTime))/(WDEnd-WDStart),MAX(networkdays(StartDateTime+1,EndDateTi me-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

"vezerid" wrote in message

ps.com...

How about...

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

HTH
Kostis Vezerides

On Feb 15, 5:54 pm, "Olly" wrote:
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?

--
Olly

NETWORKDAYS alternative, for use without Analysis ToolPak
 

Hello Olly,

Can you confirm exactly what your requirements a

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(E ndDateTime))),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" wrote:

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

"vezerid" wrote in message
oups.com...
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

On Feb 15, 7:50 pm, "Olly" wrote:
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(IS NA(MATCH(INT(StartDateTime),PublicHolidays,0)))),0 ,IF(INT(StartDateTime)=INT(EndDateTime),((EndDateT ime-StartDateTime))/(WDEnd-WDStart),MAX(networkdays(StartDateTime+1,EndDateTi me-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

"vezerid" wrote in message

ps.com...

How about...

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

HTH
Kostis Vezerides

On Feb 15, 5:54 pm, "Olly" wrote:
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?

--
Olly

NETWORKDAYS alternative, for use without Analysis ToolPak
 

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


=(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(StartDateTime)&":"&INT(E ndDateTime))),2)<6),--ISNA(MATCH(ROW(INDIRECT(INT(StartDateTime)&":"&INT (EndDateTime))),PublicHolidays,0)))-1)*(WDEnd-WDStart)+IF(AND(WEEKDAY(EndDateTime,2)<6,ISNA(MATC H(INT(EndDateTime),PublicHolidays,0))),MEDIAN(MOD( EndDateTime,1),WDEnd,WDStart),WDEnd)-MEDIAN(AND(WEEKDAY(StartDateTime,2)<6,ISNA(MATCH(I NT(StartDateTime),PublicHolidays,0)))*MOD(StartDat eTime,1),WDEnd,WDStart)

"daddylonglegs" wrote:

Hello Olly,

Can you confirm exactly what your requirements a

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(E ndDateTime))),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" wrote:

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

"vezerid" wrote in message
oups.com...
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

On Feb 15, 7:50 pm, "Olly" wrote:
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(IS NA(MATCH(INT(StartDateTime),PublicHolidays,0)))),0 ,IF(INT(StartDateTime)=INT(EndDateTime),((EndDateT ime-StartDateTime))/(WDEnd-WDStart),MAX(networkdays(StartDateTime+1,EndDateTi me-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

"vezerid" wrote in message

ps.com...

How about...

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

HTH
Kostis Vezerides

On Feb 15, 5:54 pm, "Olly" wrote:
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?

--
Olly

NETWORKDAYS alternative, for use without Analysis ToolPak
 

Brilliant! Many, many thanks.

--
Olly

"daddylonglegs" wrote in message
...
This formula will work when StartDateTime and EndDateTime are any time,
even
at weekend, evenings etc.


=(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(StartDateTime)&":"&INT(E ndDateTime))),2)<6),--ISNA(MATCH(ROW(INDIRECT(INT(StartDateTime)&":"&INT (EndDateTime))),PublicHolidays,0)))-1)*(WDEnd-WDStart)+IF(AND(WEEKDAY(EndDateTime,2)<6,ISNA(MATC H(INT(EndDateTime),PublicHolidays,0))),MEDIAN(MOD( EndDateTime,1),WDEnd,WDStart),WDEnd)-MEDIAN(AND(WEEKDAY(StartDateTime,2)<6,ISNA(MATCH(I NT(StartDateTime),PublicHolidays,0)))*MOD(StartDat eTime,1),WDEnd,WDStart)

"daddylonglegs" wrote:

Hello Olly,

Can you confirm exactly what your requirements a

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(E ndDateTime))),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" wrote:

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

"vezerid" wrote in message
oups.com...
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

On Feb 15, 7:50 pm, "Olly" wrote:
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(IS NA(MATCH(INT(StartDateTime),PublicHolidays,0)))),0 ,IF(INT(StartDateTime)=INT(EndDateTime),((EndDateT ime-StartDateTime))/(WDEnd-WDStart),MAX(networkdays(StartDateTime+1,EndDateTi me-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

"vezerid" wrote in message

ps.com...

How about...

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

HTH
Kostis Vezerides

On Feb 15, 5:54 pm, "Olly" wrote:
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?

--
Olly

NETWORKDAYS alternative, for use without Analysis ToolPak
 

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),WDE nd,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" wrote:

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


=(SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(StartDateTime)&":"&INT(E ndDateTime))),2)<6),--ISNA(MATCH(ROW(INDIRECT(INT(StartDateTime)&":"&INT (EndDateTime))),PublicHolidays,0)))-1)*(WDEnd-WDStart)+IF(AND(WEEKDAY(EndDateTime,2)<6,ISNA(MATC H(INT(EndDateTime),PublicHolidays,0))),MEDIAN(MOD( EndDateTime,1),WDEnd,WDStart),WDEnd)-MEDIAN(AND(WEEKDAY(StartDateTime,2)<6,ISNA(MATCH(I NT(StartDateTime),PublicHolidays,0)))*MOD(StartDat eTime,1),WDEnd,WDStart)

"daddylonglegs" wrote:

Hello Olly,

Can you confirm exactly what your requirements a

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(E ndDateTime))),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" wrote:

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

"vezerid" wrote in message
oups.com...
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

On Feb 15, 7:50 pm, "Olly" wrote:
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(IS NA(MATCH(INT(StartDateTime),PublicHolidays,0)))),0 ,IF(INT(StartDateTime)=INT(EndDateTime),((EndDateT ime-StartDateTime))/(WDEnd-WDStart),MAX(networkdays(StartDateTime+1,EndDateTi me-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

"vezerid" wrote in message

ps.com...

How about...

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

HTH
Kostis Vezerides

On Feb 15, 5:54 pm, "Olly" wrote:
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?

--
Olly