Timesheet help

[scottylad2]

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

Your help would be appreciated

Deek

Excel Workbook
	A	B	C	D	E	F
1
2	rostered duty	DATE	Start	End	Overtime
3	Day off	01/03/2009	06:45	17:55	11:10
4	Day off	08/03/2009	07:45	16:00	8:15
5	Day off	11/03/2009	16:00	2:08 AM	10:08
6	Day off	20/03/2009	18:45	6:00 AM	11:15
7	Day off	28/03/2009	22:45	6:45 AM	8:00
8				Total Hours	48:48
9
10	=MOD(D3-C3,1)	copied down
11	=SUM(E3:E7)
12	E3-E7 Formatted [h]:mm
13
Sheet1

 

[scottylad2]

sorry that my formulas have displayed twice

 

[scottylad2]

bumping this as i'm sure there's an answer, just i'm not able to work it out

 

[lenze]

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

 

[scottylad2]

Excel Workbook
	A	B	C	D	E	F
1
2	rostered duty	DATE	Start	End	Overtime
3	Day off	01/03/2009	06:45	17:55	11:10
4	Day off	08/03/2009	07:45	16:00	8:15
5	Day off	11/03/2009	16:00	2:08 AM	10:08
6	Day off	20/03/2009	18:45	6:00 AM	11:15
7	Day off	28/03/2009	22:45	6:45 AM	8:00
8				Total Hours	48:48
9
10	=MOD(D3-C3,1)	copied down
11	=SUM(E3:E7)
12	E3-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

 

[scottylad2]

Excel Workbook
	A	B	C	D	E
1
2	rostered duty	DATE	Start	End	Overtime
3	Day off	01/03/2009	06:45	18:45	12:00
4	Day off	08/03/2009	04:00	16:00	12:00
5	Day off	11/03/2009	16:00	2:08 AM	10:08
6	Day off	20/03/2009	18:45	6:00 AM	11:15
7	Day off	28/03/2009	22:45	6:45 AM	8:00
8				Total Hours	53:23
9
10	=MOD(D3-C3,1)	copied down
11	=SUM(E3:E7)
12	E3-E7 Formatted [h]:mm
Sheet1

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

 

[scottylad2]

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
	A	B	C	D	E	F
1
2	rostered duty	DATE	Start	End	Overtime
3	Day off	01/03/2009	06:45	18:45	11:15
4	Day off	08/03/2009	04:00	16:00	11:15
5	Day off	11/03/2009	16:00	2:08 AM	9:23
6	Day off	20/03/2009	18:45	6:00 AM	10:30
7	Day off	28/03/2009	22:45	6:45 AM	7:15
8				Total Hours	49:38
9
10				0:45
11
Sheet1