Help with Excel Template

[quartet-man]

Hi, I am just now starting to work with Excell 2003 for the first time. I downloaded their
weekly timesheet with breaks. It has time in, time out, meal break, time in and time out. However, I need to add another time in and out or two for times when I had something to take care off the clock besides the lunch break. How do I do that and keep the function of the template as designed?
Thanks, Terry

P.S. Here is the template I downloaded.

http://office.microsoft.com/en-us/templates/TC060888721033.aspx?av=ZXL000&AxInstalled=1&c=0

[pam123]

Just so I'm clear ; you want to add an additional row(s) to the template?

[quartet-man]

Sort of. :)
I think it is set up to figure the times for in and out a lunch break and in and out again for time keeping. I need to add other rows for an additional out and back in for times when clocking out only once during the workday isn't enough.
I just downloaded this, and haven't entered any time yet, but knew eventually there were days that I left during the day to take care of personal things in addition to leaving for lunch. I could probably add the rows myself, but I thought that it is set up to do calculations for the time worked that day, so I wanted it to still do that if it does.

[pam123]

See if this will work for you : http://office.microsoft.com/en-us/excel/HP052009261033.aspx?pid=CH010006341033

[quartet-man]

See if this will work for you : http://office.microsoft.com/en-us/excel/HP052009261033.aspx?pid=CH010006341033

Pam, I haven't been able to do this yet due to lots of work and also my second job. I hope to do it Sunday and will post here how things go. Thanks, Terry

[quartet-man]

I managed to copy the cells, resize them to the proper size, but it is not adding all three columns together to get the total hours worked. It does when done with the original two, but not when I add the third.

[quartet-man]

I also want to ask if it is possible to set this up to make reports between various weeks. In other words, I have saved a couple as individual weeks, can I make a report that compiles the totals from each week for a few years and print out the total of hours for each week? Or am I better served to look further for a timekeeping program to use? I had searched several, but many were lots of money and had features I didn't need. This is for my own use to keep track and show my bosses, not something for a business or many people. I also couldn't decide which company to trust.
Thanks, Terry

[pam123]

As to the added columns , I can't be sure but it's possible you're running into a size limit for work sheets.
How many columns do you need really?
You may be able to manage with fewer.
If this is for your own use and to show your bosses it shouldn't be any more involved then, say tracking a month's worth of personal expenses.
The other programs would be overkill.

[quartet-man]

What I added was one more time in, time out, and break. There are times I need that due to either working 14 hours or so and taking a supper break too, or doing sort of a split shift.

[pam123]

What I added was one more time in, time out, and break. There are times I need that due to either working 14 hours or so and taking a supper break too, or doing sort of a split shift.

You shouldn't be running into any problems with that.
If I'm reading you right you just added cells for those times when you worked longer hours?
Try expanding the whole template and creating a 16 hour work sheet.

[quartet-man]

What I did was copy the cells on top (in, out, and break) and then pasted. I then had to resize the ones beneath because of overlap or something. I am at a loss, but haven't used the program really before so I am a novice.

[stylin19]

select the entire "in" row
hold down the shift key
then select the break row

you should now have the in,out & break rows hilighted/framed

now right click
select "copy"

right click again
select "insert copied cells"

you can do that as many times as you need a set of these rows.
the format stays the same and the formula will autromatically adjust for the row.

you willl have to adjust the formula in the accumulation cell on the "Total" row.

we can get to that later.


good luck

[pam123]

Thanks stylin19,
That looks like the right answer for quartet-man.

[quartet-man]

Stylin', for the most part that is how I have done it before except I used Control C to copy and used the menu up top to insert the cells. However, doing it your way gets me the same result. Both ways I chose to move cells down, but each time I have to resize the fields under. (they do not move down all of the way and maintain their size.)

I think what I am lacking is the last that you said we would get to later because it is still not adding all of the sets together.

Thank you both for your work on this. Hopefully when I do the stuff that Stylin said would need done (when they show me :)), it will work. :)

[pam123]

Consider doing it this way : http://spreadsheets.about.com/od/tipsandfaqs/qt/column_row_add.htm

Have your Excel studies taken you to using the SUM function (AutoSum) yet ? http://spreadsheets.about.com/od/tipsandfaqs/f/sum_autosum.htm

[stylin19]

Stylin', for the most part that is how I have done it before except I used Control C to copy and used the menu up top to insert the cells. However, doing it your way gets me the same result. Both ways I chose to move cells down, but each time I have to resize the fields under. (they do not move down all of the way and maintain their size.)

That's too weird, as your way works fine for me. CTRL+C, then from "Insert" on the menu bar, selecting "copied cells", the format is fine....no resizing.( I am using office 2000 not 2003)

Now re: the formulas.

The formula in the first "TOTAL" cell(merged cells Cols E & F) is this:

=IF(OR(ISTEXT(F18),ISTEXT(F21)),"Error in C12 or C15",(F18+F21))

This basically says, if there is a non-numeric in F18 or F21, return an error message "Error in C12 or C15.(Which doesn't seem quite right, the error would be in either F12 or F15 - and those cells are custom formatted to preclude a non-numeric entry)

Do you really need to know all that ? How about a simple Sum= formula ?

Let's say for chuckles & grins that you have 4 sets of in/out/break.
You want to add each IN/OUT total together to have a final sum in the TOTAL field for each day.

A simple formula would be:
=(F12+F15+F18+F21)

Select the TOTAL cell and enter the above ( cut and paste it from here should work).

Once you have enterd this in the TOTAL cell for that first day (verify it works...
enter time in/out and verify the totals are correct for each in/out occurrance; and the TOTAL for the day is correct.)

you can copy the formula ( copy the cell ) and paste it into every day's TOTAL.
Excel will automaticall adjust the formula to add the correct cells for that column.

hope this is clear....

good luck

[quartet-man]

Maybe 2003 just stinks. ;-)

I tried changing the forumla to the one you said (the number of ins and out you chose is perfect I think). It changes the display in the field as the forumla instead of 0.00 or the total. I presume I am doing something wrong.

[quartet-man]

Consider doing it this way : http://spreadsheets.about.com/od/tipsandfaqs/qt/column_row_add.htm

Have your Excel studies taken you to using the SUM function (AutoSum) yet ? http://spreadsheets.about.com/od/tipsandfaqs/f/sum_autosum.htm

Duty calls, I will try to check this out later. Thanks.

[stylin19]

check out the attachment - see if that works -

[quartet-man]

Sorry for not replying. I have been so slammed at work that I haven't had time to do this again. (Although I did see it several days ago.) I will soon.

[quartet-man]

It worked so far! At first I thought the "f" in the formula was for field, but then realized it was the name of the column and that the = meant formula (i presume). Using the same formula, but substituting the column name did the job. The final weeks total formula still works. Thanks again for you help!!

[quartet-man]

There is a new problem. I cannot get the sheet with the additional time ins and outs to print on one page. Does anyone have any ideas. I shrunk some rows, but it didn't gain me any additional print area at the bottom.