Date Calculation for Weekly Reporting

Forum Guru's,

I am working to build a weekly report that will summarize data found in a separate tab that contains simple information like.

Name, Date, Subject, Title
Bob, 6/21/2010, Call, Director IT
John, 6/25/2010, Email, VP Sales

I am looking to sum the activities like Call, Email and report on weekly volumes. The summing part is easy, building the table that will automatically tell the formula what "dates" go together is the difficult part.

My reporting period is a Fiscal Period that starts every 22nd of the month and ends the 21st. So if the 22nd falls on a Wednesday, then Wednesday, Thursday, Friday would be considered 1 reportable week. Then the following week would be 5 days long etc.

I have figured out how to do most everything other then correlate the days into a week sum.

So to close.

Flat file of data that I need to identify information together based on a weekly sum. If it was this week, 6/14 through 6/18 would all sum into 1 column. If there were 5 "calls" made during that time, the reported sum would be 5.

Am I making sense?

Thanks in advance, this forum has always been a great resource and I appreciate the folks who help us solve problems.

Ray

So I almost came to a resolution, but am missing 1 piece.

I was able to use the following function

Please Login or Register  to view this content.

D33 Contained the Start Date (example 6/22, Tuesday)
D34 Contained every day from 6/22 through 7/21.

I created a cell chart like such.

Please Login or Register  to view this content.

(I am using the day function in cell2, the text(weekday) function in cell 3 and the above function in cell 4)

Each week would return a number based off the Start Date. So week 1, week 2 etc however this is 7 day math and I need the week to always start on Monday. With the above, the weeks went from Tuesday through Monday as a 7 day week.

Any thoughts?

Originally Posted by shg

An example workbook that shows typical (sanitized) data and expected results would help.

Shg Thank you, I see how this can help.

Attached is a condensed sanitized version of the report.

There are two important tabs.

1. Weekly Activity: This tab is intended to sumarize data into weekly totals
2. MTD Activity Dump: This tab is a data dump from our CRM tool that flat lists all activities recorded in a Month to Date fashion.

The goal is to sum the totals of the "Subject" (Column B of MTD Activity Dump Tab) based on what week they were performed.

The week needs to be driven based on cell B3, Weekly Activity Tab. This date is our Fiscal close date which is always the 21st of the month. (Fiscal period is 22nd through 21st.) The weeks need to be Weekdays, Monday - Friday unless the Fiscal Start happens mid week then week 1 could be say Wednesday through Friday. Week 2 would then be the next Monday - Friday so on and so forth.

My formulas tab was me simply trying to slice and dice this out. no luck so far.

Does this help?

Originally Posted by shg

That workbook has Excel 2007+ functions like COUNTIFS, and there are #NAME? errors. Would you please post as an xslx, and check it before you do?

The #REF errors were for cells where I removed the reference data. I didn't see any #NAME errors. I did remove all I saw and re-saved

Any thoughts on this? I've hit a wall personally.