Working with consecutive 5-day work weeks

[Kwarren]

I have developed a very elaborate “Monthly” timesheet but for simplicities sake, let’s say that column “A” contains the days of the month, columns” B & C” are the “time in, time out” columns and column “D” is the “Total hours worked” for each day.

Is there a formula that will search, starting from the first day of the month, for each complete 5 day work week, Monday thru Friday (ignoring Sat & Sun), and give me the total hours worked for each week through the end of the month? (Sunday = 1 and Saturday = 7)

I would of course have these hourly totals for each week displayed in separate cells. Being a monthly timesheet, I am aware that there will be partial weeks in just about every month.

I’m hoping this can be done without any scripting.

Thanks,
Kirk

[MarvinP]

Hi Kirk,
A Pivot Table is the answer I see in my mind. Your data structure is great as discribed. You can even have two or more time ins and time outs in a single day. Pivots allow you to group your data by week (or any interval you want, and filter out weekends. You may need another column of WeekDay that will return the DOW and have this as a filter for the pivot.

If you supply a sample file I can do it and append it back.

Oh yes, if the first column is employee name you can just as easily do it for an entire company.
Also, you don't need to keep monthly sheets as you can filter by month and keep a whole year's data or decade's data in a single table/list.

[Kwarren]

Hello,

I’ve attached a very simplified version of the timesheet with just the basic formulas for calculating working hours and overtime hours. (The other fields shown are nonfunctional)

When you enter the date in L2 (1/1/11), the dates in column “A” fill in automatically with Sat & Sun shaded in blue.

I’m somewhat new to Excel and have never used pivot tables. Will the fact that the dates change with each new month, have any negative impact on how the pivot table works. My hope is that I do not have to change the layout of the sheet.

Thanks for your help,
Kirk

[MarvinP]

Hi Kirk
You had some jazzy stuff on you spreadsheet that I removed so I could show you what I was thinking.

In Pivot Tables - see the attached. I have Name, Date, In, Out, Tot Hrs, Weekday

I drag this to a pivot table and Filter by DOW (leaving out 1-Sun and 7-Sat). Then make the rows first by Name (you can have as many employees as you want) then by Date. Now for the cool part. You can set a start date - which you make as a Sunday - and group the dates by 7 days. Out comes the attache spreadsheet. I'd work a little longer on the number format as Time format only throws away days but 1 day = 24 hours so I put this in too. I could figure this out but haven't yet.

The beauty of this is that you can group by Months, Quarters, Years or Days (and group). You can keep this data all together without needing a month per sheet. You can roll over different years, many employees etc.

This is how I would do it.

[Kwarren]

Pretty cool! I will need to educate myself on pivot tables to gain a full understanding of their use.
Thanks for your help on this issue.

Kirk

[MarvinP]

Hi Kirk,

I wasn't happy with the total hours worked when grouped by week. I'm hoping it is a simple time format problem that I'm having. I've posted a question to the guru's and will get an answer soon and pass it on to you.

Pivot Tables Rock!!!

[MarvinP]

Hi Kirk,

I found my Excel Application Options set to 1904 date system. This just screwed with me for 2 days. Now the [h]:mm:ss format calculates correctly. Find the attached with correct and much better answers.

[Kwarren]

This looks good!
There was a reason why I had my timesheet set to 1904 but I will need to look at my notes to jog my memory as to why.

Thanks again for all your help!

Kirk

[MarvinP]

The 1904 is the MAC standard I believe. Here it is.
http://support.microsoft.com/kb/180162