Formula involving time after 6 pm

csenor

Board Regular
Joined
Apr 10, 2013
Messages
160
Office Version
  1. 365
Platform
  1. Windows
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.

ABCDEFG
1StartEndHours workedShift Diff StartShift Diff EndTotal DiffTotal Overtime
216:0019:00=B2-A218:0019:00=E2-D2=vlookup(OT Rate)*hours worked+(F2*1.5)
316:3017:301

<tbody>
</tbody>
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

pleeseemailme

Board Regular
Joined
Dec 26, 2013
Messages
201
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.
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
Re entering times without a colon, try:

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

csenor

Board Regular
Joined
Apr 10, 2013
Messages
160
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Apr 10, 2013
Messages
160
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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:

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
@ 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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,471
Messages
5,636,505
Members
416,919
Latest member
twc2c

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top