Help me with formula to correctly calculate unsocial hours worked

Hi there. I've just joined the forum as I'm driving my self dizzy trying to work out the solution to my problem. I'm sure it's an easy solution but I need the help of Excel experts to find it!

I've created a spreadsheet version of the timesheet used within my organisation. There is no fixed shift pattern, so the sheet needs to calculate hours worked between two hours entered into the relevant cells in columns B ('From') and D ('To') respectively. The total hours worked is calculated in column F. My formulas perhaps aren't the best, but his bit all works ok for me.

Unsocial hours are classed as any time worked before 8:00am or after 8:00pm, Monday to Friday, and all hours worked on Saturday and/or Sunday.

Columns C and E are hidden in the working spreadsheet but I've unhidden them so you can see my formulas.

Column C calculates the unsocial hours worked before 8:00AM, and that works ok as can be seen for the example entry for Monday.

Column E is supposed to calculate the hours worked after 8:00PM, but this is where the problem lies. If for example the start time is 9:30PM and the finish time is 10:30PM (1 hour worked), my formula calculates the unsocial hours as 2.5 hours (ie. the difference between the trigger time for u/s hours - 8:00PM, and the finish time).

As I said, I'm sure the solution is an easy one, and probably staring me in the face. Hoping one of the experts here can help.

Cheers

Hello from Scotland to London

Huge thanks mehmetcik for your quick reply. Unfortunately, I still get the same outcome with your formula, e.g. if I enter a 'From' time of 21:30 and a 'To' time of 22:30, it still returns 2:30 (i.e 2.5 hours) in the corresponding cell in column E, instead of 1 hour. So the formula still calculates the number of hours from the trigger time of 8:00PM rather than the total number of hours actually worked where those hours are after 8:00PM. Apologies if I'm not explaining that clearly enough.

Any further thoughts?

Thanks again, but sorry that still doesn't work - as the result in cell D7 of your worked example shows (still returns 2:30 for a start time of 21:30 and end time of 22:30)

Sorry mehmetcik,I'm confused! In my original sample spreadsheet, cells in column D are data entry - the time worked 'To'. I've edited my original sample to make it clearer. Its the formulas in the cells in column E of my spreadsheet, that aren't correctly calculating the total number of hours worked after 8:00pm

Hi John. Good question! Technically, there are no constraints on work time (and in fact that was another issue I'd identified, but I was happy to live with a manual adjustment, on the rare occasion that a finish time goes after midnight).

That's it John, works brilliantly. Thank you so much

Many thanks mehmetcik, very useful alternative formula there. Really appreciate the time and trouble you've put in to my query.

Hi John. You helped me previously by providing a formula that worked brilliantly - for which again, many thanks.

I've now found another glitch with the formula in the timesheet I'm using which as well as calculating total worked hours, has to count the number of unsocial hours worked which are classified as any hours before 08:00 and/or after 20:00.

You gave me a formula for the u/s hours worked after 20:00 (see cell E8, in sample sheet attached), but I now see that the formula I'm using for pre 08:00 is also wrong in that it counts the time difference between the start time and 08:00, irrespective of the total hours worked. So for example, if I start at 06:00 and finish at 07:30, my formula (see cell C9 in sample sheet) counts the unsocial hours as 2 hours, when it should only be 1.5 hours.

Once again, I'm going round in circles and getting cross-eyed trying to work out the solution, and I reckon you'll spot the solution straight away!

Fingers crossed!

Hi John. You helped me previously by providing a formula that worked brilliantly - for which again, many thanks.

I've now found another glitch with the formula in the timesheet I'm using which as well as calculating total worked hours, has to count the number of unsocial hours worked which are classified as any hours before 08:00 and/or after 20:00.

You gave me a formula for the u/s hours worked after 20:00 (see cell E8, in sample sheet attached), but I now see that the formula I'm using for pre 08:00 is also wrong in that it counts the time difference between the start time and 08:00, irrespective of the total hours worked. So for example, if I start at 06:00 and finish at 07:30, my formula (see cell C9 in sample sheet) counts the unsocial hours as 2 hours, when it should only be 1.5 hours.

Once again, I'm going round in circles and getting cross-eyed trying to work out the solution, and I reckon you'll spot the solution straight away!

Fingers crossed!