Week End and Week Start dates for a given Month

[dwood@atx]

Hello Everyone,

I'm looking for help with a formula. I know how to get the week end and start date for a current week but I am looking for the start and end dates for all weeks in a current month.

For example in this month October 2016
I need week ending totals for
October 7, 14, 21, 28, 31

So I assume I would need the start and end week dates for week 1- 5 in the month. Is this possible short of adding a Week number column into my data.

thanks in advance for any assistance.
Denise

 

[MarkK]

Check out the VBA.DatePart() function with the "ww" interval.

 

[plog]

I need week ending totals for
October 7, 14, 21, 28, 31

Is that how you are defining all of your month's weeks? Week 1 always ends on day 7 of the month, Week 2 always ends on day 14 of the month, etc.?

If so, you can use this to convert a date to the week number it falls in:

MonthWeek: Int((Day([YourDateFieldHere])-1)/7)+1

It will return 1-5 based on what day YourDateFieldHere occurs on. If your month's weeks don't always end 7, 14, 21 etc., then we get to have a different discussion.

 

[dwood@atx]

Is that how you are defining all of your month's weeks? Week 1 always ends on day 7 of the month, Week 2 always ends on day 14 of the month, etc.?

If so, you can use this to convert a date to the week number it falls in:

MonthWeek: Int((Day([YourDateFieldHere])-1)/7)+1

It will return 1-5 based on what day YourDateFieldHere occurs on. If your month's weeks don't always end 7, 14, 21 etc., then we get to have a different discussion.

Thanks for your reply!
The weeks would always end on a Friday (Business week from Monday to Friday). so the dates need to change depending on the month

Thanks!

 

[jdraw]

Seems your weeks start on Monday and end on Friday.
So perhaps the way to address this generally for any month (and year) is
to determine the date of each Monday (and Friday) in the year. Or find one and calculate the other based on + or - 4.

Perhaps knowing more about the issue/opportunity would allow readers to offer more focused responses.

 

[plog]

The weeks would always end on a Friday (Business week from Monday to Friday). so the dates need to change depending on the month

That's not true. Or at least not consistent with your first post. Answer these questions to fully clarify things:

What date does Week 1 of November 2016 start?
What date does Week 1 of November 2016 end?
What is the date of the last day in the last week of November 2016?

 

[dwood@atx]

That's not true. Or at least not consistent with your first post. Answer these questions to fully clarify things:

What date does Week 1 of November 2016 start?
What date does Week 1 of November 2016 end?
What is the date of the last day in the last week of November 2016?

Sorry I'm not very good at putting my thoughts down in writing.

So the first day of a month would be the first day of week 1.

For November week 1 =
Tuesday November 1st - Friday November 4th

The last day for November would be Wednesday November 30th

I am looking for achieve weekly totals for the current month
Week 1= 1st of month to Friday
Week 2 = Monday - Friday
Week 3 = Monday - Friday
Week 4 = Monday - Friday (or Monday to Month end if a 4 week Month)
Week 5 = Monday - Month end

Please let me know if I need to explain anything better.

 

[dwood@atx]

Seems your weeks start on Monday and end on Friday.
So perhaps the way to address this generally for any month (and year) is
to determine the date of each Monday (and Friday) in the year. Or find one and calculate the other based on + or - 4.

Perhaps knowing more about the issue/opportunity would allow readers to offer more focused responses.

sorry I should have been more clear in typing. It would not always be a Monday to Friday when the week is at the beginning or end of a month.
If the 1st fell on Tuesday then the week would be Tuesday to Friday.
If the month ended on a Friday for example the last week would only contain Friday.

Thanks!

 

[MarkK]

Use DatePart() to calculate your weeks from your dates, and then GROUP BY those weeks. Consider this query....

SELECT DatePart('ww',t.Date) AS WeekNum, Sum(t.Amount) AS WeeklySum, Min(t.Date) AS WeekStartDate, Max(t.Date) AS WeekEndDate
FROM YourTable AS t
WHERE Month(t.Date) = [prmMonth] And Year(t.Date) = [prmYear]
GROUP BY DatePart('ww',t.Date);

This simply groups by the week as calculated by DatePart(), and then returns everything else as an aggregate function, including the start and end dates of the week in question.

And DatePart() takes other parameters too, if you need to start weeks on different days of the week or year.

 

[dwood@atx]

Use DatePart() to calculate your weeks from your dates, and then GROUP BY those weeks. Consider this query....

This simply groups by the week as calculated by DatePart(), and then returns everything else as an aggregate function, including the start and end dates of the week in question.

And DatePart() takes other parameters too, if you need to start weeks on different days of the week or year.

I think this should work! Thanks so much for the advice. I will post again if it works out!
THANKS!!!

 

[Galaxiom]

This function will return the first Monday in the month. It is constructed for Australia where Microsoft screwed up the first day of the week. You may need to adjust it for your region.

Public Function FirstMonday(ByVal MonthDate As Date) As Date
  
Dim SeventhOfMonth As Date
  
    SeventhOfMonth = DateSerial(Year(MonthDate), Month(MonthDate), 7)
    FirstMonday = SeventhOfMonth - Weekday(SeventhOfMonth, vbUseSystemDayOfWeek) + 1
  
 End Function

 

[jdraw]

If the 1st fell on Tuesday then the week would be Tuesday to Friday.
If the month ended on a Friday for example the last week would only contain Friday.

Are you certain of this:
If the month ended on a Friday for example the last week would only contain Friday???

If the month ended on a Monday, that week would only contain Monday, I think.

 

[dwood@atx]

Use DatePart() to calculate your weeks from your dates, and then GROUP BY those weeks. Consider this query....

This simply groups by the week as calculated by DatePart(), and then returns everything else as an aggregate function, including the start and end dates of the week in question.

And DatePart() takes other parameters too, if you need to start weeks on different days of the week or year.

So this does work for me but I'm curious if you know of a way to make it display a week even when it has a $ 0 result.

I have 3 different regions that I will run this query for and I need to put them side by side in a report. currently when I run the report if one of the regions has a $0 value for a week it just repeats the current weeks total.

Here is the Adjustment I made to the SQL
SELECT DatePart('ww',Bookings.PODate) AS WeekNum, Sum(Bookings.USValue) AS WeeklySum, Min(Bookings.PODate) AS WeekStartDate, Max(Bookings.PODate) AS WeekEndDate
FROM Bookings
WHERE (((Month([Bookings].[PODate]))=Month(Date())) AND ((Year([Bookings].[PODate]))=Year(Date())) AND ((Bookings.Region)="Ajax"))="Int"))
GROUP BY DatePart('ww',Bookings.PODate);