• Status: Solved
• Priority: Medium
• Security: Public
• Views: 304

# Access 2000 - Custom week calculation

Dear Experts,

I need to calculate weeks in Access from date following such logic

- if the date is smaller than today --> week0
- if the date is this week --> week0
- if the date is next week --> week1
- if the date is after next week --> week2

Basically the actual week is from monday to sunday, so as today is 2011-05-13 that is 6 by this formula - TodayWeekday: Weekday(Date()). This can have importance that the next week always starts at Monday so when the TodayWeekday is 2.

thanks,
0
csehz
1 Solution

Commented:
If you would want the week number to keep increasing as you extend out into the future:

``````SELECT Date() AS Today, SomeDate,
"week" & DateDiff("ww", Date(), IIf(SomeDate < Date(), Date(), SomeDate), 2) AS WeekNum
FROM SomeTable
``````
0

IT consultantAuthor Commented:
This is perfect to me, thanks very much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.