Formula involving time after 6 pm

csenor

Board Regular
Joined
Apr 10, 2013
Messages
169
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.

[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]
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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.
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
@ 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
 
Upvote 0

Forum statistics

Threads
1,223,396
Messages
6,171,866
Members
452,427
Latest member
samk379

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
Back
Top