Overtime calculation for adding a half day if after midnight

Hi,

Im new here and this is my first post!

I am making an overtime spreadsheet for my office. So far I have made a form that calculates the total hours worked and then divides that into days of 8 and a half hours. But I need a bit more than that - I need the following to be calculated:

During the week, you get half a days overtime if you work more than a 12 hour day increasing to a days overtime if you work after midnight

If you work at a weekend, you get a days pay no matter how many hours you do before midnight and an extra half day if you work after midnight (this is for weekends only so excel would have to know that the day was a weekend by referring to some sort of calendar or selecting that the day was a weekend by using a drop down list).

A day is 8 and a half hours by the way....

I don't care about calculating wages, I just need this to be calculated as a total of days based on the above criteria.

I have attached the sheet in its present state and I would like to add the above functionality to it if possible.
I am only a real novice at this and have managed to get it this far only by copying and pasting formulas (and trail and error) from this fantastic site!

I know this is a big ask and it must be really hard to sort this I guess any help would be much appreciated!

Thanks in advance!

Dan.

Helper Columns. They can be inside your table or outside it. They can, (should be,) hidden.

1. WeekDayColumn: Uses WeekDay Function on Date Column
2. OT Rate: Uses WeekDay Column and Start and End Columns To determine rate
3. DaysWorked: Uses OTRate column and start and end to determine decimal days worked for that date

In your original Total Days cell, just Sum the hidden DaysWorked Column

You're right to question this strange setup but it works for our line of work.
If you work 13 hours on a Saturday but not after midnight it still just counts as one day
If you start at 11PM Saturday and quit at 1AM Sunday it wouldn't matter if the sheet calculated it to be a day and a half because that would be very unusual and I would just recalculate it manually.
I hope that helps. I know it looks strange but on a saturday staff would always start around 9 or 10am on nearly all jobs

I dont know how the day would be calculated could we insert an extra column containing a dropdown with days of the week? (this could go next to the date column)
Am I any where near what you actually asked?!!!
I have entered some typical times in the attached sheet - some at a weekend some on a weekday