Intersection Temp Intervals

galileogali · Feb 24, 2011

I have a daily interval:
22:00 to 06:00 (a)
and an interval variable, for example: 02/13/2010 09:00 TO 02/17/2010 21:30. (b)
I want Formula that returns how many hours there are in the intersection between interval (b) and the intervals (a1... a2... an)

p45cal · Feb 24, 2011

try:

Excel Workbook

A

B

C

D

E

F

G

1

start

middle

end

total time

2

13/02/2010 09:00

17/02/2010 21:30

Sheet

Only G2 is needed, C2:D2 just shows how the formula is made up of three separate parts which need summing. That is the number of hours in the first day, + the number of full days in the middle multiplied by 8 hours, + the number of hours on the last date.
ps. the dates may look a bit wrong because I'm in the UK which uses dd/mm/yy.

galileogali · Feb 25, 2011

Thanks p45cal I am adjusting and testing

p45cal · Feb 25, 2011

I notice that cell G2 is showing only 8:00 hours above, either the format of that cell should be custom format:
[hh]:mm
(the square brackets are important),
or:
the formula needs to be a bit different (only add *24 at the end) and the format of the cell needs to be a number, but the result will be in decimal hours, so 10.5 hours means 10:30.

galileogali · Feb 26, 2011

Excel Workbook

G

H

I

J

1

Nocturnidad

2

22:00