Defining Weeks in a Month - Using First and Last Day of Month

[crogers111]

CR2011
Access 2007

I'm looking to create formulas to define/group each week in a month given the month's start date via a parameter. @BegDate

Each week begins on Sunday or the first day of the month for the first week
Each week ends on Satruday or the last day of the month for the final week of the month.

For example for Jan 2015, user would input Jan 1, 2015 in the paramater and I need formulas so I can group the data into the following the weeks:
1/1/2015 - 1/3/2015
1/4/2045 - 1/10/2015
1/11/2015 - 1/17/2015
1/18/2015 - 1/24/2015
1/25/2015 - 1/31/2015

For March 2015 the last week of the month would be: 3/29/2015 - 3/31/2015 (Sunday - Last Day of Month)

I can create a formula for @LastDayofMonth but need help defining the dates for each week.

Thanks

 

[SkipVought]

Hi,

For a company, and this is my experience over the past 30 years at 4 aircraft manufacturing firms, this data ought to be stored in a WorkdayCalendar table, where working days, holidays, accounting days/weeks and any other calendar-related data is stored, in order to unambiguously define this kind of data. It should never be derived any other way in order to avoid errors.

Right off the top in your example, there's an ambiguity! 1/1 to 1/3 is not a week! It is part of 12/28/2014 to 1/3/2015. We are takin' 'bout WEEKS, right? SEVEN days, right? So which month does this week belong to? HUGE AMBIGUITY! Needs clarification!!! And just because I think it should be such 'n' such does not make it so. Your company has defined that, I'd guess, in the accounting department, by a director or vp.

but you're not really taking about weeks. Seems you're referring to the week that the month starts and ends in and the weeks between. In this case ther can be 6 weeks in a month as in May of this year.

Just wanted to raise some issues to gain some clarification.

 

[crogers111]

Thanks for the feedback. Apologies if the above was not clear.

Sorry for using the word 'weeks' creating ambiguity. Let's call it 'date ranges'

Each date range begins on Sunday or the first day of the month for the first date range
Each date range ends on Satruday or the last day of the month for the final date range of the month.

For example for Jan 2015, user would input Jan 1, 2015 in the paramater and I need formulas so I can group the data into the following the date ranges:
1/1/2015 - 1/3/2015
1/4/2045 - 1/10/2015
1/11/2015 - 1/17/2015
1/18/2015 - 1/24/2015
1/25/2015 - 1/31/2015

For March 2015 the last date range of the month would be: 3/29/2015 - 3/31/2015 (Sunday - Last Day of Month)

Yes, May would have 6 'date ranges':

5/1/2015 - 5/2/2015
5/3/2015 - 5/9/2015
5/10/2015 - 5/16/2015
5/17/2015 - 5/23/2015
5/24/2015 - 5/30/2015
5/31/2015 - 5/31/2015

My data has a single date field that I need group into these date ranges for summaries.

I appreciate any assistance.

 

[SkipVought]

So what have you tried thus far and where are you having problems? If something is not working, what are the results?

 

[crogers111]

I've searched the forums
I have these formulas that give me the Sunday to Saturday date range I need that each date falls in:
{tbl_WorkingData.Date} + (7 - weekday({tbl_WorkingData.Date}))
{tbl_WorkingData.Date} - (Weekday({tbl_WorkingData.Date} -1))

but they obviously fail for the begining date range at the begining of a month and ending date range at the end of the month.
so I'am trying to figure out the logic and syntax for the beginning and end of month.

 

[BettyJ]

Try the following and see if it works:

Create a formula,
@InNeededMonth
[tt]if month({YourDateField})=month({?Begdate}) then 1 else 0[/tt]

In Record Selection
[tt]{@InNeededMonth}=1[/tt]

Insert-->Group
YourDateField
in ascending order
Under
The section will be printed
for each week

Insert Summary
YourDateField
Minimum
(Place it in the Group Header)

Insert Summary
YourDateField
Maximum
(Place it in the Group Header)

 

[crogers111]

Still testing but I have a series of formulas that I believe will accomplish what I need...

//Saturday after RecordDate
@WeekEndingSat
{tbl_WorkingData.Date} + (7 - weekday({tbl_WorkingData.Date}))


//Sunday Prior to RecordDate
@WeekBegSunday
{tbl_WorkingData.Date} - (Weekday({tbl_WorkingData.Date} -1))

//If Saturday after Record Date is the next month, then set to last day of the record month
@WeekEndingTest
If month({@WeekEndingSat}) <> month({tbl_WorkingData.Date})
then {@LastDayofDataMonth}
else
{@WeekEndingSat}

//If Sunday before Record Date is the previous month, then set to first day of the record month
@WeekBegTest
If month({@WeekBegSun}) <> month({tbl_WorkingData.Date})
then {@FirstDayofDataMonth}
else
{@WeekBegSun}


@FirstDayofDataMonth
{tbl_WorkingData.Date}-Day({tbl_WorkingData.Date})+1


@LastDayofDataMonth
dateadd("M",1,cdate(year({tbl_WorkingData.Date}),month({tbl_WorkingData.Date}),1))-1

I appreciate the input

 

[crogers111]

Needed to adjust one formula in the event the record date was a Sunday
@WeekBegSunday
if Weekday({tbl_WorkingData.Date}) <> 1
then
{tbl_WorkingData.Date} - (Weekday({tbl_WorkingData.Date} -1))
else
{tbl_WorkingData.Date}