Hi Anyone of an Excel Nature,

I keep a months records of my hours that I work as apartment manager
for the owner on a single monthly excel sheet.

However, the owner wants weekly time sheets and he wants them sent to
him now.

I have at least a years monthly time sheets that he wants in a weekly
format.

I would still like to keep my records of my hours in a monthly format
so that I can see what I've done at an entire month's glance.

Is there a "Formula" for automating the duplication of data between the
monthly and weekly time sheets.

Is it also possible to use a "Formula" to duplicate the
"Word-Description" of work done for the data-hours recorded between the
monthly and weekly time sheets?

Can this "Formula" be made to include "Updates", that is if I happen to
change the data in the monthly sheet that it automatically
changes-updates in the weekly sheets and Visa Versa (from weekly to
monthly)?

I've looked at "Filtering", that wont work for what I need. The owner
wants days that I haven't worked included in the time sheets to
evaluate the over all work performance.

I've also tried "=" on the weekly sheet then going to the monthly
sheet and highlighting the cell to be duplicated then going back to
the weekly sheet and pressing enter. This ends up being nothing more
than copying and pasting.

I already can do that, the point is to save a lot of time here.

A "Formula" for automating the duplication of data between the monthly
and weekly time sheets is what I'm after.

It is kind of a lot of copy and pasting other wise.

If you don't know maybe you know where there might be an answer?

Thanks.

John




--
Johnz414

It is difficult to advise without knowing the layout of your monthly
timesheet. You haven't given us enough clues to be able to divine it.
Nor do we know how you want the weekly timesheet laid out.

For example, your timesheet might be like this:

Date Hours Comment
1-Jan 4.5 "This is what I did"
2-Jan 8.5 "Cleared snow"

etc. with a separate sheet for each month.

and you might want a similar format for the weekly presentation but
with just 7 days.

Here is a possible approach:

The weekly sheet contains the week start date in A2, e.g. 7-Jan-2008
A3 contains =A2+1 and this is copied down as far as A8 (which will then
contain =A7+1). By changing A2 you will get a different week showing
up.

Now in B2 of the weekly sheet we want to get the hours for 7-Jan (or
whatever the date is in A2). We could use
=OFFSET(JanSheet!B$1,DAY($A2),0)
where JanSheet is the name of January's monthly timesheet.
This will give us the value of the cell 7 rows down from B1 on JanSheet
(ie the hours for 7-Jan)

We could copy this formula to B2:C8 and we would have the week's time
data. Change A2 to 14-Jan-2008 and we get a different week's data.

An obvious problem will arise at the end of a month when the data for a
week has to come from 2 sheets. You could edit the formulas so that
for the February dates they reference FebSheet, but smarter would be to
get the formulas to adjust themselves using the INDIRECT function. You
could make B2 on the weekly sheet contain
=OFFSET(INDIRECT(TEXT($A2,"mmm")&"Sheet!A$1"),DAY( $A2),COLUMN()-1)









Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

I'm sorry,

This is the time sheet template from Microsoft that I use:

http://tinyurl.com/yo6wbp

I've very easily augmented it for a month by extending-formatting the
time sheet from a week to a month and then I have 4 weekly time sheets
for the 4 weeks of the month after that in the same book, 6 pages
total.

So, my book starts with a cover page with the Business name and address
and mine too. Followed by the monthly work sheet (usually titled "Jan
2008" - "Month Year"). Followed by 4 weekly time sheets (titled "Week
1" etc.)

I also format my monthly sheets to start from the first day of each
month and include the entire month on each monthly sheet. I don't
follow a strict 4 week cycle.

For the weekly time sheets I just add the extra or minus days of the
month onto the last week of the 4 weeks. I then start on a new book.

The owner just wants to use it for a reference to see what I'm doing
and how he can advise me to cut corners.

But looking at the Microsoft template I think should show you
everything that you need for this. If not please let me know.

However, this template includes calculations for over time. I do not
need this, I don't get over time. In fact I don't remember a single
day of working more than 7 or 8 hours.

I have not so far tempered with the "Regular Hours" formula but I have
looked at it and see how I can take the over time out and simplify it.
If that will aid in getting what I'm after by all means please let me
know.

I have taken the "Over Time" column out and I changed the "Sick Time"
column to "Misc." - I get payed a fee for lock outs that I may include
here.

Everything else is just as is from the template.

Also, if possible I would like to automate everything, not only the
"Hours" and "Description" from the monthly to the weekly time sheets
but if I can also get it to carry over the formatting from when I first
put the day and date in on the monthly time sheet to have that
automatically carry over to the weekly time sheets that would save a
lot of time too.

Thanks,

John


Bill Manville;2571221 Wrote:
It is difficult to advise without knowing the layout of your monthly
timesheet. You haven't given us enough clues to be able to divine it.
Nor do we know how you want the weekly timesheet laid out.

For example, your timesheet might be like this:

Date Hours Comment
1-Jan 4.5 "This is what I did"
2-Jan 8.5 "Cleared snow"

etc. with a separate sheet for each month.

and you might want a similar format for the weekly presentation but
with just 7 days.

Here is a possible approach:

The weekly sheet contains the week start date in A2, e.g. 7-Jan-2008
A3 contains =A2+1 and this is copied down as far as A8 (which will then

contain =A7+1). By changing A2 you will get a different week showing
up.

Now in B2 of the weekly sheet we want to get the hours for 7-Jan (or
whatever the date is in A2). We could use
=OFFSET(JanSheet!B$1,DAY($A2),0)
where JanSheet is the name of January's monthly timesheet.
This will give us the value of the cell 7 rows down from B1 on JanSheet

(ie the hours for 7-Jan)

We could copy this formula to B2:C8 and we would have the week's time
data. Change A2 to 14-Jan-2008 and we get a different week's data.

An obvious problem will arise at the end of a month when the data for a

week has to come from 2 sheets. You could edit the formulas so that
for the February dates they reference FebSheet, but smarter would be to

get the formulas to adjust themselves using the INDIRECT function. You

could make B2 on the weekly sheet contain
=OFFSET(INDIRECT(TEXT($A2,"mmm")&"Sheet!A$1"),DAY( $A2),COLUMN()-1)









Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup




--
Johnz414

Here,

I've tried applying what you've suggested Bill and this is the formula
that I come up with which doesn't seem to want to work:

=OFFSET('[Jan 2008]Jan 2008'!D$13,DAY($C13),0)

In the Evaluate Formula the remark presented is:

"The cell currently being evaluated contains a constant."

I'm kind of learning this stuff on my own as I go along. So, you may
need to run through the procedure for doing this again for me to get
it.

If you have that much patients.

John

Johnz414;2572947 Wrote:
I'm sorry,

This is the time sheet template from Microsoft that I use:

http://tinyurl.com/yo6wbp

I've very easily augmented it for a month by extending-formatting the
time sheet from a week to a month and then I have 4 weekly time sheets
for the 4 weeks of the month after that in the same book, 6 pages
total.

So, my book starts with a cover page with the Business name and address
and mine too. Followed by the monthly work sheet (usually titled "Jan
2008" - "Month Year"). Followed by 4 weekly time sheets (titled "Week
1" etc.)

I also format my monthly sheets to start from the first day of each
month and include the entire month on each monthly sheet. I don't
follow a strict 4 week cycle.

For the weekly time sheets I just add the extra or minus days of the
month onto the last week of the 4 weeks. I then start on a new book.

The owner just wants to use it for a reference to see what I'm doing
and how he can advise me to cut corners.

But looking at the Microsoft template I think should show you
everything that you need for this. If not please let me know.

However, this template includes calculations for over time. I do not
need this, I don't get over time. In fact I don't remember a single
day of working more than 7 or 8 hours.

I have not so far tempered with the "Regular Hours" formula but I have
looked at it and see how I can take the over time out and simplify it.
If that will aid in getting what I'm after by all means please let me
know.

I have taken the "Over Time" column out and I changed the "Sick Time"
column to "Misc." - I get payed a fee for lock outs that I may include
here.

Everything else is just as is from the template.

Also, if possible I would like to automate everything, not only the
"Hours" and "Description" from the monthly to the weekly time sheets
but if I can also get it to carry over the formatting from when I first
put the day and date in on the monthly time sheet to have that
automatically carry over to the weekly time sheets that would save a
lot of time too.

Thanks,

John




--
Johnz414

Hi John

If I was trying to get to where you are trying to get to I wouldn't
start from where you are!

The fact that months are not 28 days long makes solutions more
difficult than they need be.

I would keep *all* my time in one worksheet and produce different views
of it for different purposes, using filtering.

I would have columns (e.g.)
Date Month Week Day In_1 Out_1 In_2 Out_2 Hours
6Jan08 Jan-08 6Jan08 Sun 8:00 12:00 4
7Jan08 Jan-08 6Jan08 Mon 8:00 12:00 13:00 17:00 8
8Jan08 Jan-08 6Jan08 Tue 8:00 12:00 13:00 17:30 8.5
etc.

The month column (say in row 2) would contain
=TEXT(A2,"mmm-yy")
The week [commencing] column would contain
=TEXT(A2-WEEKDAY(A2)+1,"dmmmyy")
The day column would contain
=TEXT(A2,"ddd")
The hours formula could be copied from your current timesheet

All these formulas can be copied down

The date column would contain date values formatted whatever way you
prefer. A2 would have the date of the first record
A3 would contain =A2+1 and this formula could be copied down the rest
of the column

Beneath the table, following at least one empty row, in the Hours
column (say it's column I, row 100) I would put the following formula
=SUBTOTAL(9, $I$1:$I$99)

This works like SUM($I$1:$I$99) but when the table is filtered it will
only add up the visible rows.

Then I would use Data Filter AutoFilter to get drop arrows at the
top of each column.

By selecting Jan-08 in the month column I would get the monthly
timesheet for Jan, and the subtotal formula would give me the total
hours for the month.

By selecting All in the Month column and 6Jan08 in the Week column I
would get the weekly timesheet for that week and the sub-total formula
would give the total hours for the week.

If, for any reason, you ever wanted to get the total of hours worked on
Sundays you could use filtering on the Day column to do that.

If that makes sense to you, go with it.
If it doesn't, I think the alternatives would be too difficult to
describe in a message such as this.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup