Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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 Last edited by Oliver L Randle : February 6th 07 at 05:32 PM |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Calculating Networkdays and Business Hours | Excel Worksheet Functions | |||
Calculating number of hours accross days | Excel Worksheet Functions | |||
networkdays | Excel Worksheet Functions | |||
Negative Working Hours | Excel Worksheet Functions | |||
Total hours with NETWORKDAYS | Excel Worksheet Functions |