IF AND Formula

[iisailor]

Hi Guys,
im trying to create a formula on a timesheet that reads a cell with a start time and a cell with an end time. it then deduces how many hours have been worked. The formula i wish to create will subtract time for breaks.
Basically break time runs from 10:00 until 10:15 and lunch runs from 13:35 until 14:00 (not long enough if you ask me).
The formula then will have these IF's in it:
Note: G=time started, H = time ended

IF G>14:00 then H-G
IF G>10:30 & H<14:00 then H-G
IF G<10:00 & H<10:00 then H-G
IF G<10:30 & H>14:00 then H-G-TIMEVALUE("0:40")
IF G>10:30 & H>14:00 then H-G-TIMEVALUE("0:25")
IF G<10:00 & H>10:00 & H<14:00 then H-G-TIMEVALUE("0:15")

Iv been at this for days now but im not sure if its possible to do this all in one formula.
Any suggestions
Chris

 

[scottylad2]

Excel Workbook
	B	C	D	E	F
3	08:32	12:23	12:53	17:00	07:58
4	08:26	12:23	12:55	17:13	08:15
5	08:27	12:00	12:30	17:00	08:03
6					00:00
7					00:00
Period (3)

 

[scottylad2]

For my table above, you just need the sum at the start and the celles to sum at the end J$31,E3-D3+C3-B3)) the cells with the times in are formatted hh:mm and the cell F3 through to F7 are formatted [h]:mm to count the totals. I have a similar sheet running for a whole year and it works a treat. In my one i also have other stuff, so if someone is off sick they still get credited with at least 7:30 thats what the other stuff is

 

[scottylad2]

B = Time in, C=stop for lunch, D= time back and E = stop for the day.it's quite a complex sheet, but it does a whole year watch and count on Flexible work times

 

[xld]

Try this

=H2-G2
-(SUMPRODUCT(--(ROW(INDIRECT((G2*1440)&":"&(H2*1440)))>=("10:00"*1440)),--(ROW(INDIRECT((G2*1440)&":"&(H2*1440)))<("10:15"*1440)))
+SUMPRODUCT(--(ROW(INDIRECT((G2*1440)&":"&(H2*1440)))>=("13:35"*1440)),--(ROW(INDIRECT((G2*1440)&":"&(H2*1440)))<("14:00"*1440))))/1440

 

[scottylad2]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
2	16/02/2009	In	Lunch Out	Lunch In	Out	Time for Day	Running Total	Weekly Hours	Plus Time	Minus Time	Comments	START	STOP	TOTAL
3	Monday	08:32	12:23	12:53	17:00	07:58	7:58:00	37:30:00
4	Tuesday	08:26	12:23	12:55	17:13	08:15	16:13:00
5	Wednesday	08:27	12:00	12:30	17:00	08:03	24:16:00
6	Thursday					07:30	31:46:00				hol
7	Friday	08:44	11:30	12:00	14:30	05:16	37:02:00	37:02:00		-0:28:00
Period (3)

 

[scottylad2]

Most of the formulas are typed once then copied down. The start stop at the end counts additional paid oertime, so if you weren't using your flexi hours that day and wanted paid you enter the additional hours worked for a day in there and it sends they hours to a different cell for overtime

I have this set up for a whole year and it works like a dream ( i figured most of it from comments posted on here )

 

[iisailor]

Hey guys,
Thanks very much, genuinely for the help, both options worked a treat and saved me more banging my head against the wall.
Thanks again to both of you for helping
Chris