Counting DD:HH:MM NETWORKDAYS and Hours

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Oliver L Randle - 06 Feb 2007 15:11 GMT

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 ad
the working days back in. I am trying to use workday to add the day
back in but cannot count the days correctly, as I am having to work th
hours out separately.This is to calculate on-hold for SLA time so tha
the deadline is suspended when it is pending the customer

Surprisingly it is the days that are giving me the problem. I am usin
nested IF macros to work out the hours and then add the hours back in t
correct for times that a job is on-hold. I am running separat
calculations to work out hours and mins/days as they are presentin
different problems.
My problem is that I need to be able to count both hours and minute
between 8:00 and 18:00 Monday to Friday, and then add it back in to th
original deadline to defer it. I had thought that th
NETWORKDAYS/WORKDAY combination would do it but they both calculat
days incorrectly for my purposes. I am counting a full day as onl
days that are complete from 8:00am to 18:00 Monday to Friaday and Exce
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 deferal
that don't begin or end during a weekend. What I really need is
NETWORKHOURS function that will do the same for hours/mins a
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 lik
this
=IF((NETWORKDAYS(B12,B13)=1),0,+IF((NETWORKDAYS(B12,B13)=2),1,+IF(NETWORKDAYS(B12,B13)=0,0,NETWORKDAYS(B12,B13)-2))
+IF((NETWORKDAYS(B14,B15)=1),0,+IF((NETWORKDAYS(B14,B15)=2),1,+IF(NETWORKDAYS(B14,B15)=0,0,NETWORKDAYS(B14,B15)-2))

Any help would be appreciate

--
Oliver L Randle

Reply to this Message

daddylonglegs - 06 Feb 2007 23:50 GMT

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)

> A problem that is causing me endless difficulties.
>
[quoted text clipped - 29 lines]
>
> Any help would be appreciated

Reply to this Message

Oliver L Randle - 13 Feb 2007 07:23 GMT

Thanks a lot for this extremely useful and brilliant - I am still trying
to untangle the rationale behind the various components!
It answers a number of problems and nearly takes me to a solution - but
- I am not just calculating hours but trying to move the clock back
accurately to reflect the period time that the project has been on-hold
for. So although I may know that the project has been on hold for 350
Hrs 15 mins and 22 secs I also need to defer the putative deadline by
the same period.
I notice that you have managed to get Excel to recognise 3/4 within
networkdays as 18:00 hrs but I have tried the same thing with WORKDAY
but it will not see it in the same way.

I have tried:

=IF(WORKDAY(B23,B23)>3/4,(B23+1),B23)

-to no avail. (I have separated out days and hours again to facilitate
multiple 'on hold' times)

I also need to be able to specify 1/3 or 08:00 as the time that WORKDAY
hours are counted from.

daddylonglegs Wrote:

> Hello Oliver,
>
[quoted text clipped - 65 lines]
> >
> > Any help would be appreciated

Signature

Oliver L Randle

Reply to this Message

David Biddulph - 13 Feb 2007 13:13 GMT

I think you may need to look again at the syntax of the WORKDAY() function.
Why are you adding B23 working days to your B23 start date?

Signature

David Biddulph

...

> I notice that you have managed to get Excel to recognise 3/4 within
> networkdays as 18:00 hrs but I have tried the same thing with WORKDAY
[quoted text clipped - 6 lines]
> -to no avail. (I have separated out days and hours again to facilitate
> multiple 'on hold' times)

...

Reply to this Message

daddylonglegs - 14 Feb 2007 00:54 GMT

Hello again Oliver,

I'm not sure what you're trying to do with that formula. If you have a start
date/time in A3 and a number of hours to add ( e.g. 350:15:22) in B3 then you
can get the projected date/time, based on a 08:00 to 18:00 workday with this
formula

=WORKDAY(A3,INT(B3*12/5)+(ROUND(MOD(A3,1)+MOD(B3,5/12),9)>3/4))+MOD(A3,1)+MOD(B3,5/12)-IF(ROUND(MOD(A3,1)+MOD(B3,5/12),5)>3/4,5/12)

or you can break it down into one cell for the time, e.g. in E3

=MOD(A3,1)+MOD(B3,5/12)-IF(ROUND(MOD(A3,1)+MOD(B3,5/12),9)>3/4,5/12)

and then the date in F3 with this formula

=WORKDAY(A3,INT(B3*12/5)+(E3<MOD(A3,1)))

Note: in both cases I've assumed that the start date/time will be some time
within working hours

> Thanks a lot for this extremely useful and brilliant - I am still trying
> to untangle the rationale behind the various components!
[quoted text clipped - 88 lines]
> > >
> > > Any help would be appreciated

Reply to this Message