Excel Help.... (1 Viewer)

[Andrew S]

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

 

[MarkMel]

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

 

[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

 

[John_Bonner]

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).

 

[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

 

[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