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

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.

1 Solution
Patrick MatthewsCommented:
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

Open in new window

csehzIT consultantAuthor Commented:
This is perfect to me, thanks very much
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.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now