How to calculate Week and Period from an input Date 2

[tweetyng]

Hello All,
I would like to calculate Week Number (each year has 52 weeks) and period during that week. First Day of the week is Sunday and last day is Saturday.

Example:

If I enter these days below:

Date:
01/01/2008
01/03/2008
01/15/2008
03/28/2008
07/01/2008
07/22/2008
09/27/2008
12/21/2008
12/27/2008
12/28/2008
12/31/2008

Output shoulb be:

Date Week Number Period
01/01/2008 1 01/01/08 - 01/05/08
01/03/2008 1 01/01/08 - 01/05/08
01/15/2008 3 01/13/08 - 01/19/08
03/28/2008 13 03/23/08 - 03/29/08
07/01/2008 27 06/29/08 - 07/05/08
07/22/2008 30 07/20/08 - 07/26/08
09/27/2008 39 09/21/08 - 09/27/08
12/21/2008 52 12/21/08 - 12/31/08
12/27/2008 52 12/21/08 - 12/31/08
12/28/2008 52 12/21/08 - 12/31/08
12/31/2008 52 12/21/08 - 12/31/08

Because each year should have 52 weeks only so, from 12/28/08 to 12/31/08 we should count them week 52 and from period 12/21/08 to 12/31/08 and so on on the next year.

So please help, I'm very appreciated. Thanks alots.

TN (USA). I'm using Windows XP, Access 2003, Crystal Reports 11 and SQL Server

 

[SgtJarrow]

I think you are going to have write your own function set to calculate the values you are looking for. The problem lies in you are not using a standard convention. You week 1 will almost always be short and your week 52 will almost always be long.

To get a week from a particular date, you can use:

DatePart("ww", dteDate)

To modify this to fit your logic, something like this:

IIf(DatePart("ww", dteDate) > 52, 52, DatePart("ww", dteDate))

This will simply replace any 53 with a 52.

The period portion of your request is a bit more involved. You will need to first determine the week of the date you have passed. From this week number, determine the Sunday for that wee and the Saturday for that week, probably using a combination of DateSerial, DatePart, and DateAdd functions. Of course, you have to account for week 1 (you only need the Saturday date for this as the DateSerial can be infused with a 1/1.) and for week 53 (you will need to back up any 53 week date to a 52 week date and base you Sunday date off of that. The Saturday date of week 52/53 can be found by infusing the 12/31 date of the year.)

Give it some thought and let me know how you fair. Let me know what you tried and the results you get so I can help you find the solution that meets your needs...

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[missinglinq]

Here's another piece of the puzzle:

FirstDayOfWeek = dteDate-(weekday(dteDate)-1)

LastDayOfWeek = dteDate - Weekday(dteDate) + 7

Now all you have to do is to figure out a hack for the FirstDay/LastDay for Week 1 and Week 53

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[missinglinq]

Okay, NG, here's some code that will do the job, I think. I've done some moderate testing using 2007-2009 and found no glitches yet.

dteDate = #12/31/2009#
WeekNumber = Format(dteDate, "ww")

If WeekNumber = 1 Then
    FirstDayOfWeek = "1/1/" & Year(dteDate)
    LastDayOfWeek = dteDate + (7 - Weekday(dteDate))
    Period = FirstDayOfWeek & " - " & LastDayOfWeek
End If

If WeekNumber > 1 And WeekkNumber < 53 Then
    FirstDayOfWeek = dteDate - (Weekday(dteDate) - 1)
    LastDayOfWeek = dteDate - Weekday(dteDate) + 7
    Period = FirstDayOfWeek & " - " & LastDayOfWeek
End If

If WeekNumber = 53 Then
    WeekNumber = 52
    FirstDayOfWeek = (dteDate - (Weekday(dteDate) - 1)) - 7
    LastDayOfWeek = "12/31/" & Year(dteDate)
    Period = FirstDayOfWeek & "-" & LastDayOfWeek
End If

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[tweetyng]

Thank you very much to everyone that has helped me. Once, thanks alots. Especially, The Missinglinq...These codes worked like charms. These are exactly what I'm trying to do.

TN (USA). I'm using Windows XP, Access 2003, Crystal Reports 11 and SQL Server

 

[missinglinq]

Glad we could help!

Linq

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[SgtJarrow]

Linq always provides valuable help....have a star.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[missinglinq]

About the final code I posted:

dteDate = #12/31/2009#

was there for testing purposes and should be deleted.

If WeekNumber > 1 And WeekkNumber < 53 Then

has a typo (can you spot it?) and should be

If WeekNumber > 1 And WeekNumber < 53 Then

This would only cause a bump if the WeekNumber was 53, and it didn't really affect the code, because the following If...End If would take care of the problem. It just aggravates me!

Linq

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[tweetyng]

Oh Yeah, I got that one Missinglinq...Thanks

TN (USA). I'm using Windows XP, Access 2003, Crystal Reports 11 and SQL Server

 

[tweetyng]

I have a tiny problem related to this one. The Date is the unique key and can not be duplicated (dteDate = me.txtCollectedDate).So how can I avoid the duplicate if user enter the Date which already has the record in the table and would like to pop-up a message saying "Duplicate Record. Please re-enter the date!" Please help, thanks.

TN (USA). I'm using Windows XP, Access 2003, Crystal Reports 11 and SQL Server

 

[skyline666]

Hi tweetyng,

Do you have the period and week number stored in different fields? If so, if the user enters a date that is already there, then you could point the user to that record, or display the results in a message box (not sure how or if you display the period/week number to the user after entering the date).

What you can also do is use Exit Sub to exit, so something like this:

If dteDate = Me.txtCollectedDate Then
    MsgBox("Date already exists, period is " & txtPeriod & " and week number is " & txtWeekNumber & ".", vbOkOnly, "Some relevant title here")
    If MsgBox("Duplicate record, do you want to re-enter the date?", vbQuestion + vbYesNo, "Duplicate Date") = vbNo Then
        Exit Sub
    ElseIf MsgBox("Duplicate record, do you want to re-enter the date?", vbQuestion + vbYesNo, "Duplicate Date") = vbYes Then
        Me.txtCollectedDate.SetFocus
    End If
End If

Where the Me.txtCollectedDate.SetFocus line is setting focus to wherever the user was entering the date (if not here then put wherever it was instead of the Me.txtCollectedDate)

Hope this helps you.

Andrew