need help with Breaking down Hours

Hi guys,
im sorry to disturb you,
but i have spent forever and a day trying to solve this. and im running out of time.

i have attached a sheet that im working on.

basically the issue is i have a start and end time

i need to Break this down into sections

so

start end total break workinghours shiftday day night sat sun
19:30 07:30 12 1 11 Thu 2 9 0 0
19:30 08:00 12.5 1 11.5 Fri 4.5 0 6 0


the rules are

06:00 on a week day is a Day untill 20:00
20:00 - 0600 is Night
Friday Night runs 20:00 till 00:00
Saturday is 00:00 (fri) to 00:00 (Sat)
sunday is 00:00 (sat) - 00:00 (sun)

Can anyone help me.

can't post a link or attachments sorry it wont let me
hopefully this is enough information

There was a very similar request a week or two ago here > https://www.excelforum.com/excel-gen...ml#post4956518 which may give you some helpful pointers.

I believe that you should be able to upload a sample file, however the paper clip icon doesn't work if that is the method you attempted.
To upload a sample workbook (not a picture or pasted copy) click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
Let us know if you have any questions.

Instead of attacking this with long formulas, this proposal breaks the task down into a helper table (AE4:AS10) which may be moved and/or hidden for aesthetic purposes.
The are a large number of formulas used so I won't attempt to list them.
The range actual outcomes are populated using assignments to the appropriate cells in the helper table.
Let us know if you have any questions.

I believe that I have rectified the issue.
1) Column AH now identifies a shift started and completed on Saturday.
2) Column AI identifies a shift started on Saturday and ended on Sunday
3) Column AN is populated using:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.