Hello,
I have an Overtime tracking sheet. Any hours worked between 6pm and 6am get a shift differential of an extra $1.50 per hour. Not everyone will work after 6 pm.
I'd like the Shift Differential to automatically calculate when I put the time into the start and end columns. I need a formula for D2 and E2. My thought for D2 is =if (or(a2, a3) is between 18:00 and 06:00, put 18:00, "") For E2, I need the later of the two times. Any help would be appreciated. I know Mr. Excel speaks about the Mod function in some of his YouTube videos.
P.S. - Is there a way to type a military time into a cell without having to type the colon? I know I can create a custom number format to exclude the colon from the cell after the time is typed in, but my experience has been that I still have to type the colon. I want to type 1830 without the colon.
[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[TD]Hours worked[/TD]
[TD]Shift Diff Start[/TD]
[TD]Shift Diff End[/TD]
[TD]Total Diff[/TD]
[TD]Total Overtime[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]16:00[/TD]
[TD]19:00[/TD]
[TD]=B2-A2[/TD]
[TD]18:00[/TD]
[TD]19:00[/TD]
[TD]=E2-D2[/TD]
[TD]=vlookup(OT Rate)*hours worked+(F2*1.5)[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]16:30[/TD]
[TD]17:30[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I have an Overtime tracking sheet. Any hours worked between 6pm and 6am get a shift differential of an extra $1.50 per hour. Not everyone will work after 6 pm.
I'd like the Shift Differential to automatically calculate when I put the time into the start and end columns. I need a formula for D2 and E2. My thought for D2 is =if (or(a2, a3) is between 18:00 and 06:00, put 18:00, "") For E2, I need the later of the two times. Any help would be appreciated. I know Mr. Excel speaks about the Mod function in some of his YouTube videos.
P.S. - Is there a way to type a military time into a cell without having to type the colon? I know I can create a custom number format to exclude the colon from the cell after the time is typed in, but my experience has been that I still have to type the colon. I want to type 1830 without the colon.
[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[TD]Hours worked[/TD]
[TD]Shift Diff Start[/TD]
[TD]Shift Diff End[/TD]
[TD]Total Diff[/TD]
[TD]Total Overtime[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]16:00[/TD]
[TD]19:00[/TD]
[TD]=B2-A2[/TD]
[TD]18:00[/TD]
[TD]19:00[/TD]
[TD]=E2-D2[/TD]
[TD]=vlookup(OT Rate)*hours worked+(F2*1.5)[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]16:30[/TD]
[TD]17:30[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]