Formula for time after 5pm

[josullivan601632]

Hi all

I am looking for some help with a formula to work out number of hours after 5pm.

I have a start and end time, which is formatted in time, from this I can work out hours worked, but I would like to add a formula into another column that recognises time being after 5pm and then adds the hours after 5pm, this can be done over a couple of columns but I can't work it out how to do it!

Any help would be greatly appreciated!

 

[awoohaw]

Maybe something like these:

Book2
	A	B	C	D	E	F	G	H
1
2	Start	End	hh:mm	decimal	hh:mm before 5p	dec before 5p	hh:mm after 5p	decimal after 5p
3	10:30:00	16:00:00	05:30:00	5.50	05:30:00	5.500000	na	na
4	10:30:00	17:00:00	06:30:00	6.50	06:30:00	6.500000	na	na
5	10:30:00	18:45:00	08:15:00	8.25	06:30:00	6.500000	01:45:00	1.75
Sheet1

Cell Formulas
Range	Formula
C3:C5	C3	=B3-A3
D3:D5	D3	=VALUE(B3-A3)*24
E3:E5	E3	=IF(TIME(17,0,0)>B3,B3,TIME(17,0,0))-A3
F3:F5	F3	=24*(IF(TIME(17,0,0)>B3,B3,TIME(17,0,0))-A3)
G3:G5	G3	=IF(B3<=TIME(17,0,0),"na",(B3-TIME(17,0,0)))
H3:H5	H3	=IF(B3<=TIME(17,0,0),"na",24*VALUE((B3-TIME(17,0,0))))

 

[AhoyNC]

See if this works for you.
Note: (17/24) in the formula is just the decimal form of 5:00 pm. {5:00 pm = 0.708333)
Column C is formatted as [H]:mm

Book1
	A	B	C
1	Start	End	Hours After 5 pm
2	10:00 AM	3:00 PM	0:00
3	10:00 AM	6:00 PM	1:00
4	6:00 PM	9:30 PM	3:30
5	8:45 PM	11:25 PM	2:40
6	5:00 PM	10:25 PM	5:25
7	7:00 AM	5:00 PM	0:00
8	7:00 AM	6:25 PM	1:25
9	2:00 AM	7:10 PM	2:10
10	2:00 PM	5:30 PM	0:30
11	4:45 PM	5:16 PM	0:16
12	4:00 PM	4:10 PM	0:00
13	5:00 PM	5:02 PM	0:02
Sheet1

Cell Formulas
Range	Formula
C2:C13	C2	=MEDIAN(0,B2-(17/24),B2-A2)