Timesheet: calculating total, regular, ot and pto hours

[hirick12]

Hi,
We use Excel for our timesheet. The formulas work well, except for the PTO (Paid Time Off). We can't get it to add to total hours without adding to regular.

Here are the forumulas:

Total Hours: =ROUND(IF((OR(B16="",C16="")),0,IF((C16< b16),((c16-b16)*24)+24,(c16-b16)*24))+if((or(e16="",f16="")),0,if((f16< e16),((f16-e16)*24)+24,(f16-e16)*24)),2)
Reg Hours: =IF(G16>=8,8,G16)
OT Hours: =IF(((C16-B16)+(F16-E16))*24>8,((C16-B16)+(F16-E16))*24-8,0)

our headers look like this:

<tbody>
[TD="class: xl67"]Date[/TD]
[TD="class: xl67"]Time
In[/TD]
[TD="class: xl67, width: 89"]Time
Out[/TD]
[TD="class: xl68, width: 64"][/TD]
[TD="class: xl67, width: 89"]Time
In[/TD]
[TD="class: xl67, width: 78"]Time
Out[/TD]
[TD="class: xl67, width: 78"]Total
Hrs[/TD]
[TD="class: xl67, width: 64"]Reg Hrs[/TD]
[TD="class: xl67, width: 82"]OT[/TD]
[TD="class: xl67, width: 82"]PTO[/TD]

</tbody>

Goals:
total hours equal reg hrs, ot and pto
reg hours never exceed 8 hours
reg hours do not include pto
entering the time adds to total and reg hours
ot is auto populated when entering time.

Any suggestions?

Rick

 

[Fluff]

For Reg Hrs try
=IF(G16-J16>=8,8,G16-J16)

 

[hirick12]

For Reg Hrs try
=IF(G16-J16>=8,8,G16-J16)

Thank you, but when I add data to the PTO, the Reg Hrs decreases. what we want is that reg hrs stays the same, but TOTAL hrs increases.

 

[Fluff]

Add the PTO to Total Hrs, then it will work

 

[hirick12]

Thanks again, but when I update the pto, both total and regular increase.

 

[Fluff]

If you subtract the pto from the total, then regular should be correct.
Can you supply some sample data & the full formulae you are using.

 

[hirick12]

Total Hours: =ROUND(IF((OR(B16="",C16="")),0,IF((C16< b16),((c16-b16)*24)+24,(c16-b16)*24))+if((or(e16="",f16="")),0,if((f16< e16),((f16-e16)*24)+24,(f16-e16)*24)),2)
Reg Hours: =IF(G16>=8,8,G16)
OT Hours: =IF(((C16-B16)+(F16-E16))*24>8,((C16-B16)+(F16-E16))*24-8,0)

[TABLE="class: cms_table"]
<tbody>[TR]
[TD="class: cms_table_xl67"]Column
B: Time In
C: Time Out
E: Time In
F: Time Out
G: Total Hours
H: Reg Hrs
I: OT
J: PTO

Rick
[/TD]
[TD="class: cms_table_xl67, width: 82"][/TD]
[/TR]
</tbody>[/TABLE]

 

[hirick12]

ok, we can close this thread. I trashed all my updates and started over and found the foible.

By switching the calculation for Reg Hrs from Total hrs to its' own calculation of the time in/time out, it allowed the pto to be added to Total hrs without affecting Reg Hrs.

Thanks for everyone's help!

Rick

 

[Fluff]

Glad you sorted it & thanks for the feedback