Hi, this is my first post here, so be gentle

I use a spreadsheet to track my working time. The way our wages are calculated alter, depending on the time of day. The basic hourly rate is incremented slightly as you go into 'unsociable hours'. I have the following formula for working out how many hours attract a premium after 10pm, based on starting mid afternoon:

=IF(B6="","",(IF(B6<=(LEFT(B5,5)+"22:00:00"),1-1,((((B6-(LEFT(B5,5)+"22:00:00"))-INT(B6-(LEFT(B5,5)+"22:00:00"))))*24)-0.75)))

This is broken down as follows:

If 'Finish Time (B6)' is blank, be blank, if 'Finish Time (B6)' is less than or equal to 'Start Date (B5 left hand 5 characters) + 22:00' then be '0'. Otherwise, work out how many hours I worked after 10pm, less the unpaid meal break. Hope that makes sense.

Now, I've moved to night shifts and am having difficulty transforming the code to cover this. We attract a premium rate between the hours of 10pm and 6am, so I am trying to modify it to show me how many hours I worked between these times.

As an example: I start work at 22:00 and work until 08:00. This is a 10 hour shift, but only the hours up to 6am count for the formula. So the number I need would be 8 (10pm til 6am). If my shift is 9pm til 8am then this is an 11 hour shift, and the figure I need would be 8 again. If I start at 11pm, the figure I am looking for would be 7 (11pm until 6am = 7 hours).

I've tried a couple of mutations but I keep either getting it working out everything up to 6am, whether the shift started before 10pm or not, or always coming up with 8.00.

I'm aiming for a formula that gives:

If Finish Time is blank, be blank, If Start Time is after or equal to 10pm AND finish time is after 6am then what is 6am minus Start Time, or if Start Time is after or equal to 10pm and Finish Time is before or equal to 6am what is Finish Time - Start Time, if Start Time is before 10pm and Finish Time is before 6am what is Finish Time - 10pm, otherwise all other options equate to 6am - 10pm = 8hours.

If it helps, the start and finish times are entered as dd/mm/yyyy hh:mm and formatted to display hh:mm

Any help will be appreciated - it's driving me scatty now