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