Hello Oliver,

You can customise a NETWORKDAYS formula to give you working hours between
two specific time/dates

If your start time/date is in A2 and end time/date in B2 then this formula
will calculate weekday hours between 08:00 and 18:00

=(NETWORKDAYS(A2,B2)-1)*5/12+MOD(B2,1)-MOD(A1,1)

format as [h]:mm

note: the above is only appropriate to use when A2 and B2 are always within
the working hours. If A2 and/or B2 may be at weekends or evenings use

=(NETWORKDAYS(A2,B2)-1)*5/12+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),3/4,1/3),3/4)-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),3/4,1/3)



"Oliver L Randle" wrote:


A problem that is causing me endless difficulties.

I want to be able to count hours/days in a working week (fine -
NETWORKDAYS function and WORKDAYS to add back in ....but) and then add
the working days back in. I am trying to use workday to add the days
back in but cannot count the days correctly, as I am having to work the
hours out separately.This is to calculate on-hold for SLA time so that
the deadline is suspended when it is pending the customer.

Surprisingly it is the days that are giving me the problem. I am using
nested IF macros to work out the hours and then add the hours back in to
correct for times that a job is on-hold. I am running separate
calculations to work out hours and mins/days as they are presenting
different problems.
My problem is that I need to be able to count both hours and minutes
between 8:00 and 18:00 Monday to Friday, and then add it back in to the
original deadline to defer it. I had thought that the
NETWORKDAYS/WORKDAY combination would do it but they both calculate
days incorrectly for my purposes. I am counting a full day as only
days that are complete from 8:00am to 18:00 Monday to Friaday and Excel
treats a working day as any hours between one working day and the next.
Correcting by taking away days in the formula only works for deferals
that don't begin or end during a weekend. What I really need is a
NETWORKHOURS function that will do the same for hours/mins as
NETWORKDAYS does for days and then I could really simplify the Macro!
Sorry if this is all a bit scrambled but I am new to this.

My attempt to correct for complete days only is using statements like
this:
=IF((NETWORKDAYS(B12,B13)=1),0,+IF((NETWORKDAYS(B1 2,B13)=2),1,+IF(NETWORKDAYS(B12,B13)=0,0,NETWORKDA YS(B12,B13)-2)))
+IF((NETWORKDAYS(B14,B15)=1),0,+IF((NETWORKDAYS(B1 4,B15)=2),1,+IF(NETWORKDAYS(B14,B15)=0,0,NETWORKDA YS(B14,B15)-2)))

Any help would be appreciated




--
Oliver L Randle