Formula involving time after 6 pm

[csenor]

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]

 

[pleeseemailme]

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]

Re entering times without a colon, try:

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

 

[csenor]

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]

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?

 

[mfexcel]

Hi PaddyD, I found this formula is Great. Thanks for sharing it!
I write an explanation of how it works:
Time Conversion – How to convert 24hr input as 4-digit number into TIME in Excel? | wmfexcel
Hope you like it!

 

[PaddyD]

@ 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