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

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

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.

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?
--
David Biddulph

"Oliver L Randle" wrote in
message .. .

....
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)
....

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





"Oliver L Randle" wrote:


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,

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
-




--
Oliver L Randle