Work out amount of hours in given time slot

[Alpacino]

Hi all,

i would like to workout how many hours per time slot.

but the format is in 1000-1900

the time slots will be:
0900-1200
1200-1800
1800:2200

so the answer will be :
02:00
06:00
01:00


thanks in advance

 

[Andrew Poulsom]

Try eg:

=(TEXT(RIGHT(A1,4),"00\:00")-TEXT(LEFT(A1,4),"00\:00"))

formatted as hh:mm.

 

[Alpacino]

Hi andrew thanks for the reply. How do I measure how many hours there are per time slot.

is there a formula I could use.

if someone works 1000-1900
between time slot 0900-1200 will be 02:00
between time slot 1200-1800 will be 06:00
between time slot 1800-2200 will be 01:00


thanks in advance

 

[Andrew Poulsom]

Example (formula in B2 copied across):

Excel 2010
	A	B	C	D
1		0900-1200	1200-1800	1800-2200
2	1000-1900	2:00	6:00	1:00
Sheet5

Cell Formulas
Range	Formula
B2	=TEXT(MIN(RIGHT($A2,4),RIGHT(B1,4)),"00\:00")-TEXT(MAX(LEFT($A2,4),LEFT(B1,4)),"00\:00")

 

[Alpacino]

Thank you great stuff