equation for hours short on time sheet

harveya915

Board Regular
Joined
Sep 4, 2015
Messages
117
I created a time sheet to keep track of my hours worked. My time sheet begins on the Saturday after Friday (payday) and is 2 weeks long. For example if I get paid on Friday, July 2 then the next pay period begins on Saturday July 3 through Friday July 16. I have the sheet calculate on a daily basis how many hours worked for that day and it subtotals how many hours worked in total. Based on a 8 hour work day, 80 hours/pay period, I need an equation to tell me how many hours I'm short or how many hours extra hours I have depending on the most recent hours that were input. For example let's say I worked 7 hours on Monday and Tuesday and worked 6 hours on Wednesday, so I have accumulated 20 hours in 3 days, however by the end of the day on Wednesday i should be at 24 hours, therefore I am 4 hours short.

Thanks for any input.
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Crystalyzer

Well-known Member
Joined
Oct 18, 2011
Messages
688
Office Version
  1. 365
Platform
  1. Windows
how about this?

Book1
ABC
1Hours WorkedHours Short
23-Jul71
34-Jul72
45-Jul64
56-Jul111
67-Jul1
78-Jul1
89-Jul1
910-Jul1
1011-Jul1
1112-Jul1
1213-Jul1
1314-Jul1
1415-Jul1
1516-Jul1
Sheet6
Cell Formulas
RangeFormula
C2:C15C2=COUNTA($B$2:B2)*8-SUM($B$2:B2)
 

harveya915

Board Regular
Joined
Sep 4, 2015
Messages
117
Is there a way to do it without having to add column C? For the hours short to be displayed on one cell and it changes as the days go by.
 
Last edited:

harveya915

Board Regular
Joined
Sep 4, 2015
Messages
117
Pay Period Start Date:7/3/21Pay Period End Date:7/16/21
DayTime InTime OutTime InTime OutTotal
Saturday7/3/210:008
Sunday7/4/210:0016
Monday7/5/218:00 AM4:00 PM8:0024
Tuesday7/6/2111:00 AM1:00 PM2:15 PM6:15 PM6:0031
Wednesday7/7/2111:30 AM1:00 PM2:00 PM8:00 PM7:3039
Thursday7/8/2111:30 AM1:00 PM2:00 PM6:30 PM6:0047
Friday7/9/2111:00 AM1:00 PM2:00 PM6:00 PM6:0055
Saturday7/10/210:0063
Sunday7/11/210:0071
Monday7/12/2112:00 PM1:00 PM2:00 PM7:40 PM6:408
Tuesday7/13/219:00 AM1:00 PM2:00 PM7:00 PM9:008
Wednesday7/14/210:008
Thursday7/15/210:008
Friday7/16/210:008
Total Hours49:10
Rate Per Hour0
Total Pay$0.00


This is my sheet. I tried your function on the far right hand side, I don't know where I went wrong.
But somewhere along the bottom I would like to put a cell that would tell me how many hours short or over I am at any given day. As you can see on 7/13 I'm at 49:10 hours, but I should be at 56 (5 day work weeks M-F, 8 hrs/day. Weekends to do overtime or catchup), therefore on the cell I would like for it to tell me that i'm 6:50 short. Or if I have put in extra hours it would tell me that I am above than what I should be at.
 

Crystalyzer

Well-known Member
Joined
Oct 18, 2011
Messages
688
Office Version
  1. 365
Platform
  1. Windows
I can't see your columns and rows, but basically I used this formula under "Total Pay"

Excel Formula:
=G16-(NETWORKDAYS(B2,NOW())*8)

Where
G16 = Total Hours
B2 = Start Date (7/3/21) cell
 

Forum statistics

Threads
1,141,864
Messages
5,709,066
Members
421,611
Latest member
Lisa W

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