I have a report where I'm grouping based on week number, and I need to
translate the week number to the starting Monday of that week. This is the
format expression that I'm using
=Month(DateAdd(DateInterval.Day,((Fields!Week.Value-1)*7),DateValue("January
1, " & Year(Now()))))& "/" &
Day(DateAdd(DateInterval.Day,((Fields!Week.Value-1)*7),DateValue("January 1,
" & Year(Now()))))
It works most of the time; however, it gives me some weird results when a
month or day ends with a zero. For example, October 8 is coming back 141/8
Any suggestions on the format expression I should use to convert the week
number to a date value?I solved my problem. I was trying to go about it the hard way.
Originally I had my query set up to return week(new_date) for the week
number. I added to my query to also select new_date for the date value.
Then in report designer, I kept my group for the columns of my matrix as
week number, but for the expression for the group header, I chose to display
the date value.
"Joel Lindstrom" <joel.lindstrom@.CEI.COM> wrote in message
news:A41C4802-4422-4956-A4E1-761E5B1A395E@.microsoft.com...
>I have a report where I'm grouping based on week number, and I need to
>translate the week number to the starting Monday of that week. This is the
>format expression that I'm using
> =Month(DateAdd(DateInterval.Day,((Fields!Week.Value-1)*7),DateValue("January
> 1, " & Year(Now()))))& "/" &
> Day(DateAdd(DateInterval.Day,((Fields!Week.Value-1)*7),DateValue("January
> 1, " & Year(Now()))))
> It works most of the time; however, it gives me some weird results when a
> month or day ends with a zero. For example, October 8 is coming back
> 141/8
> Any suggestions on the format expression I should use to convert the week
> number to a date value?
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment