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

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

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

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

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

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