Time formulas

[Meucci98]

Hi, I am developing a time sheet and need help with the formulas for ST, OT & DT columns.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Start[/TD]
[TD]Finish[/TD]
[TD]Breaks[/TD]
[TD]ST[/TD]
[TD]OT[/TD]
[TD]DT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]7:00[/TD]
[TD]23:00[/TD]
[TD]1:00[/TD]
[TD]8[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
In the course of a day:

• the first 8 hours are at ST (straight time)
• the next 2 hours after 8 are at OT (over time)
• any hours after 10 hours are at DT (double time)

In the above example I need:

• ST to show 8 hrs
• OT to show 2 hrs
• DT to show 5 hrs

Thank you in advance for any help.

 

[dreid1011]

Perhaps something like this:

ST formula = =MIN((C2-B2-D2),TIME(8,0,0))
OT formula = =IF((C2-B2-D2)>=TIME(10,0,0),TIME(2,0,0),(C2-B2-D2)-TIME(8,0,0))
DT formula = =MAX((C2-B2-D2)-TIME(10,0,0),0)

C2 is Finish time
B2 is Start time
D2 is Breaks

 

[Rick Rothstein]

• the first 8 hours are at ST (straight time)
• the next 2 hours after 8 are at OT (over time)
• any hours after 10 hours are at DT (double time)

Give this a try...

E2: =MIN(8,24*(C2-B2-D2))

F2: =MAX(0,MIN(2,MIN(10,24*(C2-B2-D2)-8)))

G2: =MAX(0,24*(C2-B2-D2)-E2-F2)''

Note: You will probably have to manually change the cell's formats to General as I think Excel will try to help by making them Time (or Date) formats once you place the formulas in the cells.

 

[Roxxien]

Hello,

The logic for your problem should be the following.

ST = If(Finish-Start-Break>8,8,Finish-Start-Break)
OT = If(ST=8,If(Finish-Start-Break>10,2,Finish-Start-Break-8),0)
DT = If(DT=2,Finish-Start-Break-10,0)

You only need to replace Finish, Start and Break by the good cells.

 

[Meucci98]

Hello,

The logic for your problem should be the following.

ST = If(Finish-Start-Break>8,8,Finish-Start-Break)
OT = If(ST=8,If(Finish-Start-Break>10,2,Finish-Start-Break-8),0)
DT = If(DT=2,Finish-Start-Break-10,0)

You only need to replace Finish, Start and Break by the good cells.

Thank you very much, this works great