Calculation of ordinary hours, daily and period overtime

Hi Guys and girls!
I am hoping you may be able to help with an excel formula to calculate, ordinary hours, overtime (time and a half and double time). Overtime is calculated daily and period.
Hope it is possible in excel.
Any help would be greatly appreciated.
I have attached example.

Monday to Friday
• First 10 hours between 7am and 10pm are classed as “Ordinary Hours”
• Then first 3 hours are overtime as “Time and a Half”
• Every hour after is overtime as “Double Time”
Saturday from 8am to 8pm, first 3 hours are overtime as “Time and a Half”
Sunday from 8am to 8pm all hours are overtime as “Double Time”
Calculation of Overtime – daily and period
• Daily Overtime is calculated over 10 hours outside 7am to 10pm schedule
• In Addition, “Period Overtime” kicks in after ordinary hours accumulates to 38 hours per week and calculated for the rest of the hours as.
o First 3 hours are overtime as “Time and a Half”
o Every hour after is overtime as “Double Time”

What is the excel formula to calculate the end time based on start time, break and duration please?
Day Start Time Break End Time Total Hours
Monday 7:00 0.45 ? 7.60


What is the excel formula to calculate the duration based on start time, break and end time please?
Day Start Time Break End Time Total Hours
Monday 7:00 0.45 15:21 ?

Thank you in advance.

Hi Aligw,
Thank you for your help, yes that answers the two questions on calculating the end time.
However the main question is on how to calculate the ordinary time and overtime as per initial question below.
Example expected outcome is on the spreadsheet that was attached columns F, G, H and I.
I have clarified the question and re-attached the sheet called Daily and Period Overtime V2.
Hope this helps. Let me know if you need more information please.

Monday to Friday
• First 10 hours between 7am and 10pm are classed as “Ordinary Hours”
• Then first 3 hours are overtime as “Time and a Half”
• Every hour after is overtime as “Double Time”
Saturday from 8am to 8pm, first 3 hours are overtime as “Time and a Half”
Sunday from 8am to 8pm all hours are overtime as “Double Time”
Calculation of Overtime – daily and period
• Daily Overtime is calculated over 10 hours outside 7am to 10pm schedule
• In Addition, “Period Overtime” kicks in after ordinary hours accumulates to 38 hours per week and calculated for the rest of the hours as.
o First 3 hours are overtime as “Time and a Half”
o Every hour after is overtime as “Double Time”

Many thanks once again.