Date subtraction formula

[Kepa]

Aloha,

If anyone would be so kind to help me with a formula I would be so grateful. I have the following formula:
=(NETWORKDAYS(A1,B1)-1)*11/24+MOD(B1,1)-MOD(A1,1))

I’m subtracting two dates while also subtracting out nights and weekends (11 operating hours).
The formula works fantastic for some examples but not others:

Cell A1 = 02/15/13 04:48 PM & Cell B1 = 02/15/13 04:48 PM C1 = 0:00:00
But I get a negative number with the following example. The only difference is the dates, the times are the same.
Cell A1 = 02/16/13 04:48 PM & B1 = 02/16/13 04:48 PM C1 = #####

Or

Cell A1 = 01/16/13 04:14 PM & Cell B1 = 01/17/13 10:21 AM C1 = 5:07:00
But I get a negative number with the following example
Cell A1 = 01/16/13 09:12 PM & B1 = 01/17/13 09:32 AM C1 = #####

Mahalo,

Kepa

[daddylonglegs]

Your formula should work OK for start and end times that are within working hours. The first negative number is caused because 16th February 2013 was a Saturday. In your last example 09:32 AM and 09:12 PM can't both be within an 11 hour working day. Assuming a working day 08:00 to 19:00 you can use this revised formula to work with start/end dates/times that may be outside working hours

=(NETWORKDAYS(A1,B1)-1)*("19:00"-"8:00")+IF(NETWORKDAYS(B1,B1),MEDIAN(MOD(B1,1),"8:00","19:00"),"19:00")-MEDIAN(NETWORKDAYS(A1,A1)*MOD(A1,1),"8:00","19:00")

[Kepa]

Thank you so much daddylonglegs! I thought that was my problem but I would never have gotten there without you. This works beautifully.

Mahalo nui loa,

Kepa

[arlu1201]

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.

[Kepa]

Thank you arlu1201. I did look for the "mark as solved" but wasn't able to locate. I did however add to daddylonlegs reputation. As I was very appreciative of the quick accurate response but also for giving the explanation as to why my formula was incorrect.

Thanks again.

Kepa