Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Return the Week from a date 3

Status
Not open for further replies.

SimonPGreen

IS-IT--Management
Mar 8, 2004
116
GB
Hi,

Is it possible to return a week number from a date?

i.e. 2 for todays date.

Furthermore is it possible to correlate a week with a month?

Regards,

Simon
 
DatePart("ww", Date) should get you the week number. I'm not sure what you mean about correlating a week with the month.
 
Code:
DatePart("ww", Date)
gives you the week

The second part is a bit more complex. The default "week" is Sunday to Saturday and "ww" counts the number of Sundays plus 1 that have occured after January 1st.
For example
Code:
DatePart ("ww", #01/02/2000#)
returns "2" because January 1st of that year was a Saturday.

Unfortunately DateAdd doesn't work the same way as DatePart. DateAdd just interprets "ww" as seven days so
Code:
DateAdd ( "ww", 2, #01/01/2000# )
Yields 01/15/2000 (i.e. it added 14 days to the date). That's not only not Jan 2 ... it's not even in the week Jan 2 - Jan 8 (i.e. the second week starting on Sunday.)

Given all this, if you wanted to answer a question like
In which month does week 32 fall?
Then you must first specify which day of that week exactly. Assuming it's the first day of the week (i.e. the Sunday) then
Code:
WeekNumber = 1
n = 0
WeekDate = #01/01/2000#
TargetWeek = 32
MonthNumber = 0
Do While True
   WeekDate = DateAdd ("d", 1, WeekDate)
   If WeekDay(WeekDate) = 1 [COLOR=black cyan]' Sunday[/color]
      WeekNumber = WeekNumber + 1
      If WeekNumber = TargetWeek Then
         MonthNumber = Month(WeekDate)
         Exit Do
      End If
   End If
Loop
 
Great, informative post by Golom (as usual)


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
 
How about something as oddball as this.[lol]

Format(DateSerial(Year(Date()),Month(Date())-Month(Date())+1,0)+(TargetWeekInt * 7),"mmmm")


Paul
 
Code:
Date1=#02/01/2000#
[COLOR=red]TargetWeekInt = 5[/color]
Format(DateSerial(Year(Date1),Month(Date1)-Month(Date1)+1,0)+(TargetWeekInt * 7),"mmmm")
Returns "February" but
Code:
DatePart ( "ww", #02/01/2000# )
[red]Returns "6"[/red]

The problem is that, if you're going to use DatePart's definition of a "Week" (see above), then you can't just count 7-day intervals beginning on January 1st as this does.
 
Sorry, I posted that reply just as I was leaving town for the weekend. Maybe I should have stayed out of town.[auto]
I agree with Duane that Golom's post is very informative. My little code snipette was just to try and determine the month that a week fell in (the second part of the OP's question). I know the inherant problems with the DateAdd/DateDiff functions, but thought if you are just looking for the Month, it might be fairly simple with an expression similar to the one I posted.

Paul
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top