Return the Week from a date 3

[SimonPGreen]

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

 

[rjoubert]

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

 

[Golom]

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 1^st.
For example

DatePart ("ww", #01/02/2000#)

returns "2" because January 1^st of that year was a Saturday.

Unfortunately DateAdd doesn't work the same way as DatePart. DateAdd just interprets "ww" as seven days so

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

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

 

[dhookom]

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]

 

[PaulBricker]

How about something as oddball as this.

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


Paul

 

[Golom]

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

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 1^st as this does.

 

[PaulBricker]

Sorry, I posted that reply just as I was leaving town for the weekend. Maybe I should have stayed out of town.
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