# IF AND Formula

#### iisailor

##### Board Regular
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

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

##### Well-known Member
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

##### Well-known Member
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

##### Banned
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

##### Well-known Member
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

##### Board Regular
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

Replies
3
Views
241
Replies
8
Views
299
Replies
1
Views
68
Replies
9
Views
114
Replies
3
Views
110

1,191,671
Messages
5,987,958
Members
440,122
Latest member
branhill

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back