Calculating hours outside of certain times

[davidcox9]

I'm trying to calculate the number of hours outside of 7am - 3pm. I need a formula to update the # of hours to all be 8 hours and I haven't figured it out yet.

Time in	Time out	# of hours	What I need it to be
11:00:00 PM​	7:15:00 AM​	8.25​	8.00​
11:00:00 PM​	11:00:00 AM​	12.00​	8.00​
11:00:00 PM​	7:00:00 AM​	8.00​	8.00​
			I can't get the right formula to get the above number

 

[Legacy 143009]

How about:

=((24*60/1440)-IF(A2<15*60/1440,15*60/1440,A2))+IF(B2>7*60/1440,7*60/1440,B2)

 

[davidcox9]

I'm just getting

#VALUE!

 

[Legacy 143009]

I don't know. Please find the working sample:

WeTransfer - Send Large Files & Share Photos Online - Up to 2GB Free

WeTransfer is the simplest way to send your files around the world

we.tl

 

[davidcox9]

I made a slight change to get the correct format I need. Thanks for the help!
=(((24*60/1440)-IF(A2<15*60/1440,15*60/1440,A2))+IF(B2>7*60/1440,7*60/1440,B2))*24

 

[davidcox9]

it works if the hours are 11pm and 7 am, can you help me with the below example. The 16 should be 8 as well as they only have 8 hours worked.

3:00:00 PM​	11:00:00 PM​	16​
11:00:00 PM​	7:00:00 AM​	8​

 

[Legacy 143009]

Then this should do it:

=(IF(A2>B2,((24*60/1440)-IF(A2<15*60/1440,15*60/1440,A2))+IF(B2>7*60/1440,7*60/1440,B2),B2-IF(A2<15*60/1440,15*60/1440,A2)))*24

 

[davidcox9]

Sorry I am just now getting to work on this again. So far this looks like it is working I appreciate it so much!

 

[Legacy 143009]

No problem. Thanks for the feedback