Calculating Working Hours Between Two Dates & Times [MON-FRI and SAT, SUN excluded]?

Hi,

I think you need to upload a workbook with some sample dates & time and the results you expect to see. It's not immediately obvious how your rules are applied given the use of Median and Mod functions in your current formula.

Hello miki12, welcome to Excel Forum

See attached - it isn't specifically adapted for your setup but perhaps you can adjust accordingly

A2 has start time/date, B2 end time/date and F2:H8 contains a table containing the work times for each day - you can adjust the work times as required, just change start and/or end times for each day and column H will update with the duration. Formula is as follows to calculate the work hours

=SUMPRODUCT(INT((WEEKDAY(A2-{1;2;3;4;5;6;7})+INT(B2)-INT(A2))/7),H$2:H$8)-MEDIAN(0,MOD(A2,1)-INDEX(F$2:F$8,WEEKDAY(A2)),INDEX(H$2:H$8,WEEKDAY(A2)))-MEDIAN(0,INDEX(G$2:G$8,WEEKDAY(B2))-MOD(B2,1),INDEX(H$2:H$8,WEEKDAY(B2)))

times/dates in columns A and B are randomly generated, press F9 to re-generate or enter your own data to test

Note that the above formula copes with any start/end times, even on Sundays or evenings outside work hours. If all you start/end times are always within working hours the formula can be simplified

Hi

Would it not be easier to redesign the worksheet to simplify it.