Calculate Time Sheet - Adding Columns

  • Thread starter Thread starter vicki_2003
  • Start date Start date
V

vicki_2003

I'm trying to set up a time sheet at work using excel. I have set up my
sheet to calculate each day of the week individually. (M, T, W, Th, F,
Sa, Su).

I was able to use the following formula to calculate an employee's
hours for one day of the week.
=min(E4-D4-L4,8/24)

I want to add the total hrs calculated for each day of the week. I'm
really unsure of what formula to use for this calculation and would
greatly appreciate your assistance.

The closest formula I can think of since it's looking for a value is
the following formula:
=sum(e4-d4-l4,8/24)+(s4-r4-z4,8/24)+(ag4-af4-an4,8/24)+(au4-at4-bb4,8/24)+(bi4-bh4-bp4,8/24)+(bw4-bv4-cd4,8/24)+(ck4-cj4-cr4,8/24).
Can you tell me what's wrong with this formula or tell me how to enter
a correct formula to add these columns. Below is a sample format.

In Out Reg Hrs Totaled Lunch In Lunch Out
Lunch Hrs Totaled OT Hrs
 
Vicki,

=sum(e4-d4-l4,8/24)+(s4-r4-z4,8/24)+(ag4-af4-an4,8/24)+(au4-at4-bb4,8/24)+(bi4-bh4-bp4,8/24)+(bw4-bv4-cd4,8/24)+(ck4-cj4-cr4,8/24)
The SUM in this case is summing e4-d4-l4 and 8/24 not what I think you intended

I think you want MIN's in front of each (a-b-c,8/24)
ie
=MIN(e4-d4-l4,8/24)+MIN(s4-r4-z4,8/24)+MIN(ag4-af4-an4,8/24)+MIN(au4-at4-bb4,8/24)+MIN(bi4-bh4-bp4,8/24)+MIN(bw4-bv4-cd4,8/24)+MIN(c
k4-cj4-cr4,8/24)

Then you can use a SUM instead of all the +'s

=SUM(MIN(e4-d4-l4,8/24),MIN(s4-r4-z4,8/24),MIN(ag4-af4-an4,8/24),MIN(au4-at4-bb4,8/24),MIN(bi4-bh4-bp4,8/24),MIN(bw4-bv4-cd4,8/24),M
IN(ck4-cj4-cr4,8/24))

But... that really doesn't save you any space or time???


Dan E
 
The parentheses are all highlighted in formula to calculate totals the
one that begins w/ =sum.
 
Not sure if this is a message to me or a general message???
If it is to me make sure that the whole formula pastes in correctly
on occasion word wrap messes up formulas...

Dan E
 
Hi Vicki,

I have a sheet that does the following

Date
Day of wk
Clock in (using 24 hour clock time for both IN & OUT)
Clock out
Hours =E6-D6 (formated with Time)
Hrs =HOUR((SUM(F5:F9))) finds the total hours in the column F (formated
with Time)
min =MINUTE((SUM(F5:F9))) does the same for minutes (formated with Time)
TOT MIN =MINUTE(SUM(F5:F9))+(HOUR((SUM(F5:F9)))*60) (formated with Time)
TOT HRS =(I6*(1/60)) (formated with Time)
Gross $ =$C$1*J6 c1 is my hourly rate, j6 is TOT HRS

I am doing some other things, like using CONDITION FORMATING to verify that
what I worked and the check shows
=IF(O6-J6<0,E1,IF(O6-J6>0,D1,IF(O6-J6=0,F1)))
Short, even, or over pd. They show up as diffierent colors as well.

This may seem complicated, just takes time to put into play.
Wayne B (e-mail address removed)
 
Back
Top