HELP! Pivot Chart help urgent!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've got a query that pulls from a table.

I view my query in Pivot Chart view, with product numbers displayed by a
line chart. My bottom axis is By Weeks, my sides going up are Units, etc.

I can tell it to sort my data by weeks on the chart view. No problem.

But instead of saying like June 1st to June 7th or June 1-7, or says a
number like "23". And the spot next to it that should be June 8-14 is "24".

How do I get it to say any variation of the date other than those
numbers????? Please help!
 
You could try writing a function that converts from week number to a range
of days.

How you do that depends on what you consider to be the 1st week, but
conceptually it would be something like the following untested air-code:

Function ConvertWeekNumber(WeekNumber As Long, WhatYear As Long) As String

Dim dtmDate As Date
Dim dtmSaturday As Date
Dim dtmSunday As Date

' Determine a date in the given week
dtmDate = DateAdd("ww", WeekNumber - 1, DateSerial(WhatYear, 1, 1))
' Determine the date of the preceding Sunday
' (NOTE: Weekday returns 1 for Sunday, 2 for Monday and so on to 7 for
Saturday)
dtmSunday = DateAdd("d", 1 - WeekDay(dtmDate), dtmDate)
' Determine the date of the following Saturday
dtmSaturday = DateAdd("d", 7 - WeekDay(dtmDate), dtmDate)

ConvertWeekNumber = Format(dtmSunday, "mmm dd") & " - " & _
Format(dtmSaturday, "mmm dd")

End Function

You might have to play around with the line dtmDate = DateAdd("ww",
WeekNumber - 1, DateSerial(WhatYear, 1, 1)) to meet your needs.
 
Back
Top