Counting hours in 2 cells but only counting whether they are in a range or not

[lisaspencer]

I am trying to create a work hours spreadsheet and I will be working some hours at a particular rate and other hours on a higher rate.
For example, if I work from 11:00pm Thursday to 7:00am Friday, I get paid £12 p/h for any hours worked between 12:00am and 6:00am, and a £10 p/h rate for any other hours. So when putting the start time and end time in my spreadsheet I would like a formula that will count the hours IF any hours fall into the £10 p/h rate from 6:00am to 12:00am, and another cell formula IF any hours fall into the £12 p/h rate from 12:00am to 6:00am.

any help would be great please!

 

[Eric W]

Try this:

Book1
	B	C	D	E
1	Start Time	Finish time	Total Hours (£10)	Total Hours (£12)
2	10/22/2020 0:00	10/22/2020 2:00	0	2
3	10/22/2020 23:00	10/23/2020 7:00	2	6
4	10/24/2020 0:00	10/24/2020 18:00	12	6
Sheet2

Cell Formulas
Range	Formula
D2:D4	D2	=(C2-B2)*24-E2
E2:E4	E2	=(MAX(0,MIN(C2,INT(C2)+6/24)-MAX(B2,INT(C2)))+IF(DAY(B2)<>DAY(C2),MAX(0,MIN(C2,INT(B2)+6/24)-MAX(B2,INT(B2)))))*24

It's likely that you can shorten the E2 formula to

=MAX(0,MIN(C2,INT(C2)+6/24)-MAX(B2,INT(C2)))*24

It just depends on how long the shifts can be, and how many days could be in the mix.