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.

A | B | C | D | E | F | G | |

1 | Start | End | Hours worked | Shift Diff Start | Shift Diff End | Total Diff | Total Overtime |

2 | 16:00 | 19:00 | =B2-A2 | 18:00 | 19:00 | =E2-D2 | =vlookup(OT Rate)*hours worked+(F2*1.5) |

3 | 16:30 | 17:30 | 1 | ||||

<tbody>

</tbody>