# Formula involving time after 6 pm

#### csenor

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

 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>

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

#### pleeseemailme

##### Board Regular
In D2 try: =IF(OR(A2>=2/3,A2<=0.25,B2>=2/3,B2<=0.25),0.75,"")
In E2 try: =B2
In F2 try: =IFERROR(E2-D2,"")

I am assuming that B2 will always be the later of the two times.

##### MrExcel MVP
Re entering times without a colon, try:

=ROUNDDOWN(A2,-2) / 2400 + MOD(A2,100) / 1440

#### csenor

##### Board Regular
In D2 try: =IF(OR(A2>=2/3,A2<=0.25,B2>=2/3,B2<=0.25),0.75,"")
In E2 try: =B2
In F2 try: =IFERROR(E2-D2,"")

I am assuming that B2 will always be the later of the two times.

Thanks Pleeseemailme. I'll give it a try.

#### csenor

##### Board Regular
Thanks PaddyD. Where would I insert this formula? Would you add it to the custom formatting drop down box in the ribbon?

I am making this form for someone else to fill out. I wanted to make it as easy as possible. Can you type a 4 digit number and make Excel recognize it as military time? Or is it the only way to put the colon in?

Last edited:

##### MrExcel MVP
@ csenor. You cannot do what you're after using custom formats. Enter the time in a cell, then use the formula in another cell to convert into time value.

@ mfexcel - the explanation is good. fwiw, I do not take credit for the formula. Took me a while to remember, but I got it from Dave McRitchie, who got it from Glenn Schwandt back in 1999:
Date & Time

Replies
4
Views
70
Replies
2
Views
261
Replies
2
Views
50
Replies
2
Views
53
Replies
2
Views
131

1,187,001
Messages
5,961,049
Members
438,516
Latest member
Fintrics

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