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
|