LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Junior Member
 
Posts: 2
Unhappy Counting DD:HH:MM NETWORKDAYS and Hours

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculating Networkdays and Business Hours James A. Excel Worksheet Functions 3 September 26th 06 08:02 PM
Calculating number of hours accross days MTLeslie Excel Worksheet Functions 6 August 22nd 06 12:39 PM
networkdays kevt Excel Worksheet Functions 1 September 8th 05 02:23 PM
Negative Working Hours Mohammed Zenuwah Excel Worksheet Functions 2 July 13th 05 07:39 AM
Total hours with NETWORKDAYS Spencer Hutton Excel Worksheet Functions 2 February 16th 05 04:36 PM


All times are GMT +1. The time now is 05:54 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"