Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.
=(LOOKUP($A$19 -LOOKUP($A$19,INDEX(Shifts,0,3),INDEX(Shifts,0,4)) + $B$19,INDEX(Shifts,0,5),INDEX(Shifts,0,3)))+($A$19 -LOOKUP($A$19,INDEX(Shifts,0,3),INDEX(Shifts,0,4)) + $B$19)-LOOKUP($A$19 -LOOKUP($A$19,INDEX(Shifts,0,3),INDEX(Shifts,0,4)) + $B$19,INDEX(Shifts,0,5),INDEX(Shifts,0,3))
It is designed for row 19 where the start time and duration are in columns A and B.
This is a bit tricky. I am offering a start:-
Open in new window
You will find this formula in the attached workbook Sheet2!K26. The sheet explains it. Refer to the rows 14 to 23 based upon which the formula is developed in row 26 with the final result in K26.Your concept of dividing the day into 2 x 12 hours is very un-Excel because Excel has long decided that a day has 24 hours. To display "military" time or AM/PM is a matter of cell formatting, not of calculation. One day has the integer 1. One minute = 1/1440. 0.5 days is both, 12 hours and 12 noon because in Excel all time starts and ends at midnight.
I suggest that the breaks during a period of work time equal all the breaks during a 24 hour period, minus breaks which ended before the start time, minus break times which commenced after the end time. As my spreadsheet shows my above formula isn't ready to calculate durations of more than 24 hours. It should also produce a wrong result if the calculated end time lands within a break. To eliminate the latter the formula would grow 2 or 3 times in size. Therefore, I would recommend using VBA if this is an issue. Accommodating work periods of more than 24 hours would also increase the formula's size.
Please take a look at the tables I have created (rows 1:10) and how they are used. When and if you implement the formula you may use cell references were I use named ranges and named ranges to specify the tables. That would make more sense. Anyway, we aren't so far from the drawing board yet. Let me know how I can be of further help.
EXX-130624-Time-Calculator.xlsx