Results 1 to 11 of 11
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Calculating week (2002)

    Hi,

    Is there a way to calculate a week based on a date? I know mmddyy for formatting but am trying to determine week 1, 2.....52.

    Thanks,
    Leesha

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Calculating week (2002)

    Have a look at <post#=344991>post 344991</post#>
    Regards
    John



  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calculating week (2002)

    Wow! That is great and opens a whole possibility of report capabilities for me. I noticed in the thread that Hans asked if the the need was for weeks in the year or weeks in the month. My original need is weeks in the year, but is there a way to get weeks in a month. I have data I need to look at based on weeks in the month that I presently calculate in Excel. It adjusts for 5 week months one year and 4 week months the next year.

    Thanks!

    Leesha

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculating week (2002)

    You will have to tell us (in words) exactly how the week in the month is to be calculated (and perhaps you can program it yourself once you have done that!)

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calculating week (2002)

    Hi Hans,

    Program it myself? In excel probably, in access?? I rather doubt it. I'm getting the hang of iif and embedded iif statements but not that good.

    Our week ending date is on Friday of the week. So, the number of weeks in the month woud be based on the number of Fridays in the month.

    Believe it or not, once I know how to determine that I do believe I've learned enough from you guys to be able to do the rest of what I need to do, without resorting to just plain calculating it in excel. My whole goal in this database is to find a way to cut down on the amount of work the agency depends on me for in excel so it frees me up for other more nursing related needs as the nursing shortage is awful. This database will in effect clone a good portion of what I do now in excel. If this week thing can't be done its not a show stopper, however it would be a great perk if it can be done. I was simply curious after reading that weeks can in fact be calculated.


    Leesha

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculating week (2002)

    If you want to know how many Fridays occur in a month, you can use these functions:

    ' Will return the first, second, third, fourth, fifth or last day of type aDay
    ' in the given year and month.

    ' aYear is the year
    ' aMonth is the month
    ' aDay can be 1=Sunday ... 7=Saturday
    ' aNum can be 1, 2, 3, 4, 5 or 9=Last

    ' Examples:
    ' DayInMonth(2002, 5, 6, 3) will return the 3rd Friday in May, 2002
    ' DayInMonth(2002, 12, 3, 9) will return the last Tuesday in December, 2002

    Function DayInMonth(aYear As Long, aMonth As Long, aDay As Long, aNum As Long) As Date
    If aNum = 9 Then
    DayInMonth = DayInMonth(aYear, aMonth + 1, aDay, 1) - 7
    Else
    DayInMonth = DateSerial(aYear, aMonth, _
    7 * aNum + 1 - WeekDay(DateSerial(aYear, aMonth, 1), aDay Mod 7 + 1))
    End If
    End Function

    ' Will return the number of aDays in a given year and month.
    ' aDay can be 1=Sunday ... 7=Saturday

    ' Example:
    ' NumberOfDaysInMonth(2004, 11, 6) will return the number of Fridays in November 2004.

    Function NumberOfDaysInMonth(aYear As Long, aMonth As Long, aDay As Long) As Long
    NumberOfDaysInMonth = (DayInMonth(aYear, aMonth + 1, aDay, 1) - _
    DayInMonth(aYear, aMonth, aDay, 1)) / 7
    End Function

    <!profile=MarkD>MarkD<!/profile> has posted a series of related functions in <post#=340905>post 340905</post#>.

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calculating week (2002)

    OMG Hans! You really thought I might be able to figure this one out on my own??? I'll have to study this one tonight. BTW, I'm sure this is painfully obvious but I haven't a clue. Why the reference to "9" in various sections of the code when there are 7 days in a week?

    Thanks,
    Leesha

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculating week (2002)

    The "9" is just a convention I used to be able to retrieve the last Friday (or Monday or ...) in a month; it might as well have been 37 or 99.

    For example, to retrieve the second Friday in December, 2004, you can use DayInMonth(2004, 12, 6, 2)
    2004 = year
    12 = month
    6 = Friday (Sunday = 1, Monday = 2, etc.)
    2 = second

    And to retrieve the last Friday in December, 2004, you can use DayInMonth(2004, 12, 6, 9)
    2004 = year
    12 = month
    6 = Friday (Sunday = 1, Monday = 2, etc.)
    9 = convention for "last"

  9. #9
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calculating week (2002)

    Thanks for the explanation. I continue to be amazed at what Access can do. If only I could get my brain to "think" like it!!! The explanations help.

    Have a good one,
    Leesha

  10. #10
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating week (2002)

    Maybe a good book about Access could help you.
    I have Access Inside Out from Helen Feddema that I find very good, but if you do a search in the forum, you'll find others that are even good.
    Francois

  11. #11
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calculating week (2002)

    Thanks! I'll check this one out. I do have a few and have found the Access Bible to be helpful at times.

    I will look into this one.

    Leesha

Posting Permissions

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