Excel 2007 : Networking hours when service window end time is less than service window start time

Hi,

Am currently using Excel 2007

I have a scenario where i need to calculate net working hours between 2 dates, excluding weekends. The recognized working hours in a day(Mon to Fri) are from 09:00 AM to 01:30 AM (next day morning). If someone works outside this service window, those hours are not counted

E.g 1 If i start on 28th Nov, 2011 at 9 AM and end on 29th Nov, 2011 at 9 AM - the net working hours should be 16.5 hrs
E.g 2 If i start on 28th Nov, 2011 at 8 AM and end on 29th Nov, 2011 at 9 AM - the net working hours should be 16.5 hrs
E.g 3 If i start on 28th Nov, 2011 at 10 PM and end on 29th Nov, 2011 at 9 AM - the net working hours should be 3.5 hrs
E.g 3 If i start on 27th Nov, 2011 at 10 PM and end on 29th Nov, 2011 at 9 AM - the net working hours should be 16.5 hrs

I have tried formula from below but it fails as the working hours end time (01:30 AM) is lesser than start time (09:30 AM)

=IF(M3<>"",(NETWORKDAYS(G3,M3,$AW$2:$AW$15)-1)*($AY$2-$AX$2)+IF(NETWORKDAYS(M3,M3,$AW$2:$AW$15),MEDIAN(MOD(M3,1),$AY$2,$AX$2),$AY$2)-MEDIAN(NETWORKDAYS(G3,G3,$AW$2:$AW$15)*MOD(G3,1),$AY$2,$AX$2),"NA")

where G3 = Start date
M3= End Date
$AW$2:$AW$15 = Holiday List
$AY$2 = 01:30 AM
$AX$2= 09:00 AM


Any support on this would be very helpful

Regards,
V

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook. Don't upload a picture when you have a workbook. None of us is inclined to recreate your data. Upload the workbook and manually add an 'after' situation so that we can see what you expect. In addition clearly explain how you get the results.

To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'.
To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.

On this page, below the message box, you will find a button labelled 'Manage Attachments'.
Clicking this button will open a new window for uploading attachments.

You can upload an attachment either from your computer or from another URL by using the appropriate box on this page.
Alternatively you can click the Attachment Icon to open this page.

To upload a file from your computer, click the 'Browse' button and locate the file.

To upload a file from another URL, enter the full URL for the file in the second box on this page.
Once you have completed one of the boxes, click 'Upload'.

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

In situations where the end time is smaller than the start time, you'll need to add a day (1), so the calculation knows it is still "larger". Give this a try:

Please Login or Register  to view this content.

Cheers,

Rather than MAX($I$2-$H$2,($I$2+1)-$H$2), you can use MOD($I$2-$H$2,1). Shorter and easier to debug. Same for replacing your If statement later on that does the same thing.

So the working hours on a Friday would be 09:00 on Friday until 01:30 on Saturday morning?

The suggestions posted so far won't deal correctly with finish times at 01:00, e.g. Thursday 21:00 to next day at 01:00 should be 4:00 hours

To cope with any start or end time and any period in H2/I2 - either on the same working day or crossing midnight - you can use this version

=IF(B2<>"",(NETWORKDAYS(A2-H$2,B2-H$2,G$2:G$15)-1)*MOD(I$2-H$2,1)+IF(NETWORKDAYS(B2-H$2,B2-H$2,G$2:G$15),MIN(MOD(I$2-H$2,1),MOD(B2-H$2,1)),MOD(I$2-H$2,1))-MIN(NETWORKDAYS(A2-H$2,A2-H$2,G$2:G$15)*MOD(A2-H$2,1),MOD(I$2-H$2,1)),"N/A")