# equation for hours short on time sheet

#### harveya915

##### Board Regular
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

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
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
 Pay Period Start Date: 7/3/21 Pay Period End Date: 7/16/21 Day Time In Time Out Time In Time Out Total Saturday 7/3/21 0:00 8 Sunday 7/4/21 0:00 16 Monday 7/5/21 8:00 AM 4:00 PM 8:00 24 Tuesday 7/6/21 11:00 AM 1:00 PM 2:15 PM 6:15 PM 6:00 31 Wednesday 7/7/21 11:30 AM 1:00 PM 2:00 PM 8:00 PM 7:30 39 Thursday 7/8/21 11:30 AM 1:00 PM 2:00 PM 6:30 PM 6:00 47 Friday 7/9/21 11:00 AM 1:00 PM 2:00 PM 6:00 PM 6:00 55 Saturday 7/10/21 0:00 63 Sunday 7/11/21 0:00 71 Monday 7/12/21 12:00 PM 1:00 PM 2:00 PM 7:40 PM 6:40 8 Tuesday 7/13/21 9:00 AM 1:00 PM 2:00 PM 7:00 PM 9:00 8 Wednesday 7/14/21 0:00 8 Thursday 7/15/21 0:00 8 Friday 7/16/21 0:00 8 Total Hours 49:10 Rate Per Hour 0 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
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

Replies
4
Views
114
Replies
1
Views
165
Replies
5
Views
268
Replies
4
Views
92
Replies
3
Views
345

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?

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