Editing a Timesheet to autocalculate hours

[minus-sign]

I'm sure there's a simple way to do this but I'm -this close- to cracking VB open and speghetti coding my way out of this mess.

Problem: I am working on a timesheet that has 6 insertion zones per day. 1(in)morning, 1(out)morning, 1(in)afternoon, 1(out)afternoon, 1(in)evening, and 1(out)evening. Currently, staff member insert the time they come in (morning, afternoon, evening) to 2 different cells of the six available that day and the time they leave onto the timesheet. They then add up the hours and enter the solution into a 7th cell.

The time sheet is designed so that staff members from different departments of the county, working 24 hours of the day, can all use it. Saves paper, but its a pain in its current form.

Is there a way to get excel to recognise the Ins and Outs without coding an application into it or am I just going to have to bite the bullet? I know how to do it with just 2 cells (1 in and 1 out per day) but 6 is spanking me.

Program: excel 2003

[Paul Komski]

Calculating 6 versus 2 inputs is not complicated I think.
If cells Ax Bx Cx Dx Ex and Fx are the six ins and outs then Gx (the total for the day) would just equal:
(Bx-AX)+(Dx-CX)+(Fx-EX)

I would never conder using a spreadsheet for such inputs and calculations - unless for just a couple of employees. A database solution would definitely be the way to go. MSAccess, Foxpro or OpenOffice would all be able to provide much more elegant solutions and in a not very complicated manner.

If you want a pointer of two in that direction the just say so.

[minus-sign]

Thanks Paul but...would that work with the sheet I'm using? It looks something like the example below


_____A____________B____________C____________D_____ _______E_________F________H___
|_In Morning_|_Out Morning_|_In Afternoon_|_Out AfterNoon_|_In Night_|_Out Night_|_______
__________________________________________________ _______________________________
|___9:00____|_____________|____________|__________ ____|________|___6:00___|_______

If I were to put that straight in, wouldn't I end up with a negative number?
=SUM(Bx-AX)+(Dx-CX)+(Fx-EX)
=(0-9)+(0-0)+(6-0)
=-9+6
=-3

Instead of nine hours. Or if I format cells A-F to time will excel adjust for the discrepancy somehow? Or am I using the wrong calculator? (something other than SUM).

Each employee of the county is responsible for their timesheet, putting in the data, etc. As far as I know anyway; this is just what we got from county. I work at the Library and not having the formulas is cumbersome when I have to recalculate (more often than I'd like) due to schedule changes and such. This month was a particular PITA which has lit a fire under me bum. Access is not available for most of our PCs (a few have one version or another on them, but the program is poofed from most). OOo or other programs not installed by IT are also not an option at work for reasons I will not get into.

I figured out how to write a macro for it. Just an input box and convert to military time in the code then to decimal then subtract the numbers pretty much like you say = total hours worked. RadBtns for morning, noon and night to control AM/PM during the translation so the macro knows that 9:00AM = 09:00 hours and 9:00PM = 21:00. Then I could even add conditions like If THWdec >= 8 then THW = THWdec - 0.5 for a half hour lunch break. We get an hour, but they only pay for half of it.

Except for 3rd shift personell (which we have none in my dept, but I like to be thorough) it would/should give the proper hours, and I think there'd be a way to get it to do their right too. But...is exchanging one PITA for another, not to mention you'd have to enable macros to use it. Yeah...I can picture all the blue hairs I work with doing that. Right...

I just...total noob at Excel/spreadsheets here. I don't know what the heck I'm doing :/

[Paul Komski]

To do it that way you must enter values for the morning, afternoon and night in pairs; no blanks.

I attach an Excel97 Spreadsheet Template you might like to use or else to give you some ideas. Unprotect the sheet from the Tools menu if you want to make any changes to it. While protected, data can only be entered into the blue and yellow cells.

[minus-sign]

Thanks for the help Paul. I'm looking at it right now.

So if I wanted to, I could do the same thing you have in steps (column 5 - a drawn out version of your previous example in post 2) near abouts but just a single SUM and achieve similar results? That does make things a lot easier than where my head was going. As long as the max of "morning out" is 12:59PM, Afternoon In/Out and Night In/Out would never enter a negative value as they start at 1:00PM on to 12:58AM. The sums of all three blocks would then equal the total hours input across all three pairs?

Thanks again for taking the time to spell that all out.

[Paul Komski]

There are of course many ways of doing things. You might want to keep the sessions as morning, afternoon, etc or simply call then Session 1, 2, 3.

SUM can be used but is best reserved for longish groups of cells or whole columns/rows from other sheets altogether.

I recommend that you consider not using time formatting for the times in question. Not only does entering the colon in the middle add complexity for users but all sorts of bizarre results can happen with calculations. Suggest entering hours and mins as integer values in their own right and then for any actual calculations convert the differences to the total minutes involved. The totals in minutes (as long as they are not negative values) can be easily resolved back into hours and minutes using =INT(value/60) and =MOD(value,60) respectively.

Another attachment to outline some of these ideas.

[wombil]

might seem a bit cheeky but a pencil works pretty well

[Paul Komski]

The lead is inclined to break in pencils. Even biros are unreliable and tend not to work if turned upside down.