The attached spreadsheet shows the intermediate calculations in the helper columns (C:H). The combined formulas are in columns K:L.
With the start time in Cell A2 and the end time in Cell B2
Revised Start =IF(WEEKDAY(A2,2)>5,INT(A2+7-WEEKDAY(A2,2)+1),A2)
Revised End =IF(WEEKDAY(B2,2)>5,INT(B2-7-WEEKDAY(B2,2)+1),B2)
elapsed time would be =IF(WEEKDAY(B2,2)>5,INT(B2-7-WEEKDAY(B2,2)+1),B2) - IF(WEEKDAY(A2,2)>5,INT(A2+7-WEEKDAY(A2,2)+1),A2)
Bookmarks