Results 1 to 14 of 14
  1. #1
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316

    Unanswered: Ascertain date that week 1 started for the current year

    I have been trying to work this one out all morning...

    How can I ascertain the date of when week 1 started for any given year? So, let's say my input date is 7th Jan 2007, I would expect an output of 31st Dec 2006 (first DayOfWeek of first week of 2007). If my input is 5th Jan 2008, I would expect an output of 30th Dec 2007 (first DayOfWeek of first week of 2007). All this is based on the assumption that first DayOfWeek is a Sunday.

    Any help much appreciated.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    So let me get this straight...
    If 7th Jan 2007 was a sunday, you'd want to return Sunday 31st Dec 2006?
    Kinda sounds like it might be easier to have a hard coded date set in a table...
    but I'm sure r937 will tell you otherwise

    It might also be useful to know this:
    Weekday(date, [firstdayofweek])
    you'd be wanting Weekday(date, acSunday)

    This returns an integer value representing each day of the week.

    Try simplifying your problem a wee bit... It's a bit of a mouthful. Break it down into processing steps.

    - GeorgeV
    George
    Home | Blog

  3. #3
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    So let me get this straight...
    If 7th Jan 2007 was a sunday, you'd want to return Sunday 31st Dec 2006?
    Yes! Even if the input date was 1st Jan 2007, I'd still want an result of 31st Dec 2006.

    Kinda sounds like it might be easier to have a hard coded date set in a table...
    I was thinking about this, but it didn't seem like an especially elegant solution. Still, it would keep things simple.

    It might also be useful to know this:
    Weekday(date, [firstdayofweek])
    you'd be wanting Weekday(date, acSunday)

    This returns an integer value representing each day of the week.
    I'm already using something similar to that to ascertain the WeekOfYear of any given date.

    Try simplifying your problem a wee bit... It's a bit of a mouthful. Break it down into processing steps.
    I have, and I failed, which is why I posted here!

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Haha ok!

    So for ANY Sunday in 2007 you want to return the very last Sunday of 2006?
    aand for ANY Sunday in 2008 you want to return the very last Sunday of 2007?

    Do you want to do this for any given Monday, Tuesday, Weds... etc? Return the last one in the year before?

    I imagine we can use the Weekday to find the day of the week and I'm almost certain there's a weeknumber function - combine the two and I reckon we have a solution!

    What say you?
    George
    Home | Blog

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Um ... This 1 is SOOOOOOO simple ... By your own reckoning, get the day of week for the 1st day of the desired calender year then subtract that number of days from that 1st day ... You might need to play with a +/- 1 offset to get the right day ... The DateAdd function just might come in handy
    Back to Access ... ADO is not the way to go for speed ...

  6. #6
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Does this do what you want

    Code:
    Function FirstSundayOfYear(ByVal ThisYear As Integer) As Date
        Dim Firstday As Date
        
        Firstday = (DateSerial(ThisYear, "01", "01"))
        
        If Weekday(Firstday, vbSunday) <= 5 Then
            FirstSundayOfYear = Firstday + (1 - Weekday(Firstday, vbSunday))
        Else
            FirstSundayOfYear = Firstday + (8 - Weekday(Firstday, vbSunday))
        End If
        
    End Function
    ??

    MTB

  7. #7
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Quote Originally Posted by georgev
    So for ANY Sunday in 2007 you want to return the very last Sunday of 2006?
    aand for ANY Sunday in 2008 you want to return the very last Sunday of 2007?
    Yes, that is indeed what I need, although I didn't really visualise it until I just skipped through the Outlook calendar... I think I know how to do it now. Thanks for the help chaps.

  8. #8
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Well, I now have another problem - my WeekNumber function isn't working as expected. I have tried 3 different WeekNumber functions found from various places on the web, and non of them will give me a result of 1 for a date of 1st Jan 2006.

    Here is the first one I was using:

    Code:
    Function fWeekNumber(InputDate As Long) As Integer
    Dim A As Integer, B As Integer, C As Long, D As Integer
        fWeekNumber = 0
        If InputDate < 1 Then Exit Function
        A = Weekday(InputDate, vbSunday)
        B = Year(InputDate + ((8 - A) Mod 7) - 3)
        C = DateSerial(B, 1, 1)
        D = (Weekday(C, vbSunday) + 1) Mod 7
        fWeekNumber = Int((InputDate - C - 3 + D) / 7) + 1
    End Function
    This looked ideal as it apparently enables you to set when a week should start. However, when you pass a date of 1st Jan 2006 (which is a Sunday), it returns a value of 52, which is clearly wrong, as it is the first week of the new year.
    Last edited by bcass; 02-14-07 at 12:44.

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I stumbled across vbFirstWeekOfYear in access help...
    Might be worth a look in
    George
    Home | Blog

  10. #10
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi again

    How about

    Code:
    Function WkNo(ByVal Thisdate As Date) As Integer
        Dim Firstday As Date
        Dim FirstSundayOfYear As Date
        
        Firstday = (DateSerial(Year(Thisdate), "01", "01"))
        
        If Weekday(Firstday, vbSunday) <= 5 Then
            FirstSundayOfYear = Firstday + (1 - Weekday(Firstday, vbSunday))
        Else
            FirstSundayOfYear = Firstday + (8 - Weekday(Firstday, vbSunday))
        End If
        
        WkNo = Int((Thisdate - FirstSundayOfYear + 7) / 7)
    End Function

    ??


    MTB

  11. #11
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Rev B

    This should take care of all the end condition ie week 53 etc.

    Code:
    Function WkNo(ByVal Thisdate As Date) As Integer
        Dim CalcYear As Integer
        Dim Firstday As Date
        Dim FirstSundayOfYear As Date
        
        CalcYear = Year(Thisdate) + 1
        Do
            Firstday = (DateSerial(CalcYear, 1, 1))
            
            If Weekday(Firstday, vbSunday) <= 5 Then
                FirstSundayOfYear = Firstday + (1 - Weekday(Firstday, vbSunday))
            Else
                FirstSundayOfYear = Firstday + (8 - Weekday(Firstday, vbSunday))
            End If
            
            CalcYear = CalcYear - 1
        Loop Until FirstSundayOfYear <= Thisdate
        
        
        WkNo = Int((Thisdate - FirstSundayOfYear + 7) / 7)
    End Function

    At least I think so !!

    Some one will let me know if not?


    MTB

    ps isn't i more usual to have Monday as the first day of the week for week numbering ?
    Last edited by MikeTheBike; 02-15-07 at 04:28.

  12. #12
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Cheers. That worked perfectly. Not sure about Monday being the norm for first day of week. Isn't the deafult WorkDay set to vbSunday in VBA?

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by bcass
    Cheers. That worked perfectly. Not sure about Monday being the norm for first day of week. Isn't the deafult WorkDay set to vbSunday in VBA?
    Correct - sunday is the default
    George
    Home | Blog

  14. #14
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Yes Sunday is the Default, but that doesn't prove anything (this is microsoft after all).

    All the desk diaries I ever seen in the last 30 years have Monday as the first week day, and weeks are numbered accordingly !?


    MTB
    Last edited by MikeTheBike; 02-15-07 at 05:43.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •