# Excel Help....

Discussion in 'Computers' started by Andrew S, Jun 22, 2005.

1. ### Andrew S Stunt Coordinator

Joined:
Sep 30, 2001
Messages:
214
0
Trophy Points:
0
I'm working on a spreadsheet for a work schedule and want to know if there's a way to calculate total number of hours for each employee per week when it's set up something like this:
Monday Tuesday Wed Thurs Friday
Pete: 9-2, 7-3, 7-3, OFF, 9-5,
etc..

At first I used:
=(COUNTIF(C1895:I1895, "8-4")+COUNTIF(C1895:I1895, "7-3")+COUNTIF(C1895:I1895, "6-2")+COUNTIF(C1895:I1895, "9-5"))*8

... but would like it to somehow interpret a non-8-hour shift if it occurs, like 8-3 for example.

If anyone has any tips they would be much appreciated.
Andrew

2. ### MarkMel Screenwriter

Joined:
Nov 19, 2003
Messages:
1,875
116
Trophy Points:
1,610
The best way to do this would be to have a start time and an end time, a seperate cell for each then calculate the time in a third cell. Then sum all of the third cells.

Go to this website for good hints.

www.tek-tips.com

3. ### Joseph DeMartino Lead Actor

Joined:
Jun 30, 1997
Messages:
8,311
13
Trophy Points:
5,610
Location:
Florida
Real Name:
Joseph DeMartino
Andrew I can send you a personal timesheet file that I use to keep track of my own hours at work. You can see the structure and copy the formulas. It uses separate start and stop time cells (4 of them, actually, since I have to deduct the lunch hour) and requires that the time be entered in 24 hour clock format. Works like a charm.

Send me an e-mail and I'll reply with a copy of the file.

Regards,

Joe

4. ### John_Bonner Supporting Actor

Joined:
Oct 25, 2000
Messages:
664
0
Trophy Points:
0
I would change the format of the cells to Time. (FORMAT, CELLS, TIME). Then have a column for Start time and a column for End time. You would enter 9-2 as 9:00 AM in the Start time column and 2:00 PM in the End time column. A third column would subtract the two and give you Time Worked as 5:00 (5 hours).

5. ### Joseph DeMartino Lead Actor

Joined:
Jun 30, 1997
Messages:
8,311
13
Trophy Points:
5,610
Location:
Florida
Real Name:
Joseph DeMartino

You'll want to format that third column as "number" with maybe one or two decimal places, rather than time, or the system will return a time-of-day value rather than total hours. (If I format the total cell as "time" on the spreadsheet I mentioned above an 8 hour day shows up as "12:00 AM". )

Regards,

Joe

6. ### Joseph DeMartino Lead Actor

Joined:
Jun 30, 1997
Messages:
8,311
13
Trophy Points:
5,610
Location:
Florida
Real Name:
Joseph DeMartino
For anyone interested in a solution to this problem, here's how my timesheet spreadsheet handles the calcuations:

Because we work a 9 hour day with an hour off for lunch, we need to enter time in for the morning, time out for luch, time in for the afternoon and then time out for the day. We're on a bi-weekly pay schedule and payroll weeks from start on Fridays. For that reason I use a formula to display the day of the week, so that if I screw up entering a date the mistake is immediately evident.

In any case, the basic spreadsheet uses 7 colums labelled and formatted as follows:

A - Day - Forumla based on date entered in column B. Displays day spelled-out, "Sunday" through "Saturday" (Format: Number, Custom, "dddd")

B - Date - Diplays as "December 16, 2005".
(Format: Number, Custom, "mmmm d, yyyy"

C - In - "8:00 AM" Input in 24 hour time format.
(Format: Number, Time, "1:30 PM")

D - Lunch, Out. Same as "C"

E - Lunch In, Same as "C"

F - Out. Same as "C". Note: for shifts that extened past midnight (24:00), simply add the extra time to "24" If an employee clocks out at 1:00 AM enter "25:00", 2:30 AM enter "26:30", etc. The display and the calculations will both be correct.

G - Contains the formula for caculating hours worked:
=(((D8-C8)*24)+((F8-E8)*24))
(Example is from line 8 of the finished form, the first on which hours worked are recorded.)
(Format: Number, Number, 2 decimal places.)

Regards,

Joe