Timesheet: calculating total, regular, ot and pto hours

hirick12

New Member
Joined
Jun 2, 2017
Messages
11
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:


DateTime
In
Time
Out
Time
In
Time
Out
Total
Hrs
Reg HrsOTPTO

<tbody>
</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
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,314
Office Version
  1. 365
Platform
  1. Windows
For Reg Hrs try
=IF(G16-J16>=8,8,G16-J16)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,314
Office Version
  1. 365
Platform
  1. Windows
Add the PTO to Total Hrs, then it will work
 

hirick12

New Member
Joined
Jun 2, 2017
Messages
11

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,314
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 2, 2017
Messages
11

ADVERTISEMENT

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)

Column
B: Time In
C: Time Out
E: Time In
F: Time Out
G: Total Hours
H: Reg Hrs
I: OT
J: PTO

Rick

<tbody>
</tbody>
 

hirick12

New Member
Joined
Jun 2, 2017
Messages
11
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,314
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,123,359
Messages
5,601,153
Members
414,431
Latest member
JustmemyselfandI

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
Top