# Timesheet help

##### Well-known Member
Hi fellow Excelers, looking for the last bit of code that will make my Timesheets do exactly as I need.

The daily overtime, when someone works additional hours at the end of their scheduled day i've sussed by myself. And thanks to someone on here and regular contributor mgirvin, ive been able to add on a column where, if someone works a whole days extra overtime it can be calculated. I was having issues because some of the overtime days go from 1 day into the next and calculating the hours was giving me errors but mgirvin posted a solution on his website at Youtube that fixed that for me.

The last part that i need to address, is if someone works a days overtime, then i need to be able to deduct 00:45 mins from their total hours worked. Using the formula Mike posted, i'm getting formula error messages if i try and include the subtraction in the same formula. I've no available space to add new columns on my actual sheet so i really need an informula solution, but one that also does what it can do just now which is count hours correctly when the shift spans day 1 into day 2

Deek

Excel Workbook
ABCDEF
1
2rostered dutyDATEStartEndOvertime
3Day off01/03/200906:4517:5511:10
4Day off08/03/200907:4516:008:15
5Day off11/03/200916:002:08 AM10:08
6Day off20/03/200918:456:00 AM11:15
7Day off28/03/200922:456:45 AM8:00
8Total Hours48:48
9
10=MOD(D3-C3,1)copied down
11=SUM(E3:E7)
12E3-E7 Formatted [h]:mm
13
Sheet1

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

##### Well-known Member
sorry that my formulas have displayed twice

##### Well-known Member
bumping this as i'm sure there's an answer, just i'm not able to work it out

#### lenze

##### Legend
I'm sure there's an answer too, but I'm (and I suppose others), not clear as to what you want
lenze

##### Well-known Member
Excel Workbook
ABCDEF
1
2rostered dutyDATEStartEndOvertime
3Day off01/03/200906:4517:5511:10
4Day off08/03/200907:4516:008:15
5Day off11/03/200916:002:08 AM10:08
6Day off20/03/200918:456:00 AM11:15
7Day off28/03/200922:456:45 AM8:00
8Total Hours48:48
9
10=MOD(D3-C3,1)copied down
11=SUM(E3:E7)
12E3-E7 Formatted [h]:mm
13
Sheet1

What i want, is to be able to some how incorporate into this formula and show in E3 =MOD(D3-C3,1) an additional subtraction of 45 minutes from the Mod formula. I've tried, =MOD(D3-C3,1)-00:45 but i'm getting an error message. I need to show the total hours worked, but minus 45 minute meal break in an overtime expenses claim form. i don't have any more column space to put in a new column.

Sorry for my description Lense, i have the picture in my bonce, just not the words!!

any help appreciated

##### Well-known Member
Excel Workbook
ABCDE
1
2rostered dutyDATEStartEndOvertime
3Day off01/03/200906:4518:4512:00
4Day off08/03/200904:0016:0012:00
5Day off11/03/200916:002:08 AM10:08
6Day off20/03/200918:456:00 AM11:15
7Day off28/03/200922:456:45 AM8:00
8Total Hours53:23
9
10=MOD(D3-C3,1)copied down
11=SUM(E3:E7)
12E3-E7 Formatted [h]:mm
Sheet1

For instance, where i have 12 hours shoing as a total, i need it to show 11.15

##### Well-known Member
Well i seem to have come with an answer to my own problem but it's a little crude. without an answer though i'm stumped, my preferred option was for something that i could write into the formula so if anyone looks at this and can help me thanks in advance. What i did was place the 45 minutes in another cell and wrote the formula to so that it deducts whatevers in that cell and it seems to work. Ive made the font white and no border so it won't show in the printing but as i said i'd be happier if there was something i could put in the original formula and not use a helper cell

In my actual sheet you can't see the 0:45 in D10

Excel Workbook
ABCDEF
1
2rostered dutyDATEStartEndOvertime
3Day off01/03/200906:4518:4511:15
4Day off08/03/200904:0016:0011:15
5Day off11/03/200916:002:08 AM9:23
6Day off20/03/200918:456:00 AM10:30
7Day off28/03/200922:456:45 AM7:15
8Total Hours49:38
9
100:45
11
Sheet1

1,191,670
Messages
5,987,954
Members
440,121
Latest member
eravella

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