I'm currently making a worksheet to calculate the working days between two dates and times, however I'm running into a problem when attempting to deduct lunch break hours. I've split the formula into 2 parts for this post: the first part , the second part deducts hours taken for lunch.
  • [@Start] = Start date & time in MM/DD/YY HH:MM Format
  • [@End] = End date & time in MM/DD/YY HH:MM Format
  • Lunch_Break_Start = Lunch hour start time in HH:MM Format
  • Lunch_Break_End = Lunch hour End time in HH:MM Format


The first part of the formula (checks if End date is blank, calculates NETWORKINGDAYS (in hours) between 2 dates):
=IF([@End]="","",((NETWORKDAYS([@Start],[@End])-1)*(Daily_End_Time-Daily_Start_Time)+IF(NETWORKDAYS([@End],[@End]),MEDIAN(MOD([@End],1),Daily_End_Time,Daily_Start_Time),Daily_End_Time)-MEDIAN(NETWORKDAYS([@Start],[@Start])*MOD([@Start],1),Daily_End_Time,Daily_Start_Time)))

The second part continues from the first part:
-IF((MOD([@End],1)<Lunch_Break_Start),((NETWORKDAYS([@Start],[@End])-1)*(Lunch_Break_end-Lunch_Break_Start)),((NETWORKDAYS([@Start],[@End]))*(Lunch_Break_end-Lunch_Break_Start)))

Together, it looks like this:
=IF([@End]="","",((NETWORKDAYS([@Start],[@End])-1)*(Daily_End_Time-Daily_Start_Time)+IF(NETWORKDAYS([@End],[@End]),MEDIAN(MOD([@End],1),Daily_End_Time,Daily_Start_Time),Daily_End_Time)-MEDIAN(NETWORKDAYS([@Start],[@Start])*MOD([@Start],1),Daily_End_Time,Daily_Start_Time)))-IF((MOD([@End],1)<Lunch_Break_Start),((NETWORKDAYS([@Start],[@End])-1)*(Lunch_Break_end-Lunch_Break_Start)),((NETWORKDAYS([@Start],[@End]))*(Lunch_Break_end-Lunch_Break_Start)))

It all seems to work correctly, UNLESS the end date is on 3/31/12 and the end time is in the AM. It is adding an extra hour to the duration when this happens (177:00 instead of 176:00). Additionally, after adding the second half of the formula, the initial 'check if @End is blank' step fails for blank cells, and #VALUE! is displayed. I've attached the file, as well.
ResourceDuration.xlsx
Any help on this would be greatly appreciated! Thank you.