Help with time formulas start/end/hrswork/ot

[Drew69]

Hello

I am new to this forum and would love to get some help. I am trying to get all the times aligned and not getting it to work. Every time I fix one the other one fails. I am not good with rules as I am a beginner with excel.

I have two different formulas going right now and I am stuck. I could use the help. At the end of the schedule is a totals column for the weekly hours. The only one working right is the late column because it's not really tied to the bigger cells. I need the late column to minus hours from the weekly total hrs of 37.5 per week.

I was hoping to get this going for Monday at the beginning of the shift. Hope someone can assist me. The headings in red are the two different formulas.

Need: The total hrs worked column to deduct the 30 min lunch. If they stay an hour of OT it will show up as the below. The OT column to calculate the OT with 15 min increments. 1.25, 1.5, 1.75 etc.. depending on how long the employee stays back.


START END TOTAL HRS WRK OT LATE
7:00 AM 4:00 PM 8.5 1

[AliGW]

Welcome to the forum.

Where have you shown your expected results? I am not completely clear where we should be looking.

[Drew69]

The formula is in the spreadsheet in the highlighted headings. If I try to explain it since I might confuse everyone.

Columns
D E F G
Start End Tot hrs work OT

1st OPT: The formula so far I have is =(E3-D3)*24 which is in the total hrs column. This is in the OT column =IF(F3<=8,0,F3-8)

2nd OPT: In the other highlighted column formula is =(T3-S3)*24-0.5 total hrs work and in the OT column =U3-7.5

1st option almost does what I wanted but the OT column only goes up by the hour and not in 15 min increments. The OT will be wrong
and the 2nd OPT goes into the - when I delete the start and end time instead of going to 0 like option 1.

I am probably going to confuse everyone I apologize in advance. I am not good with rules. The late column ignore as I will have the last column minus that from the total weekly hours.

[bebo021999]

You are trying to show what formula you are using, but fail to show us: what is your expected outcome, in value

Try to show us your manual results with:
Case 1: 7:00 - 3:00: how many is hrs wrk, how many is OT, and why?
Case 2: 7:00 - 4:00
...
try to go thru all special cases (representative cases)