1. 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. Re: Calculating week (2002)

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

3. 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. 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. 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. 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. 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. 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. 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. Re: Calculating week (2002)

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.

11. 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
•