I need help refining an Excel formula using 12-hour clock entries that calculates A) the end time of a task based on variable start times and B) accounts for lunch and break allowances, and C) adjusts for one or more days if duration + break(s) + lunch(s) cause end time to extend into the next day(s) should decision be to convert to 24-hour clock entries in future. Formulas should be copy-able to other rows for multiple task entries. Existing formula referenced "Parameter table but does not include 12-Hour vs. 24-Hour function.
Looking to refine existing formulas [see attached "Time Calculator.xlsx"] at E12:E16 under "Planned End", where formulas utilize current but changeable Shift start, Shift end, break duration, break start, lunch duration, and lunch start times. As shown, the formula are identical. however they do not all resolve accurately, depending on duration and/or "Planned Start" time because they pick up breaks and/or lunches improperly.