YTD MTD WTD Report On Company Calendar

[jortizz]

Office 2007-


I receive weekly sales data (Excel spreadsheet) from 6 of our customers and monthly sales data from a seventh customer. Each of their "Week Ending" is slightly different. One customer's week might end on a Friday, another on a Saturday and others on Sundays. I would like to run YTD, MTD, and Week to Date sales reports based off of my company's calendar.

We end our weeks on Saturdays and refer to each week as Jan WK 1, Jan WK 2, etc... What I want to do is to take the all of our customers' week endings and just match it with my company's week, this way when I run a report I can give my users the option to select the report they want based on YTD, Month to Date, or just a specific week. Can anyone give me any suggestions please?

[June7]

one possible solution:
Build query with a field constructed by expression that will calculate an adjusted week ending date for each customer to agree with yours. It could be a long IIf expression (6 customers is doable) or a function that is called by the query. Then use this query as basis for report. Use this constructed field in the grouping/sorting/filtering.

I guess the seventh customer could not be included in by week summaries.

[jortizz]

It's 6 customers but we have 3 years worth of data from them. It's actually 40,000 records and many of their week ending dates do not match my company's fiscal week ending date.

[June7]

So did you try my suggestion?

Is the weekending day for each company always the same for all the years? The IIf option to construct the field would be something like:
AdjWeekEnd: IIf([company]="abc", [datefield]+1, IIf([company]="def",[datefield]+2, ...continue for remaining companies,[datefield]))))))