Brandon's Blog

4/1/2009

Following Up

So, I changed my Excel formula I mentioned in This, My Life so that it now caps the week number at 4, which turns out to be a design requirement:

=MIN(TRUNC(((WEEKDAY(DATE(YEAR(E2), MONTH(E2), 1),2)+DAY(E2)-1)-1)/7)+1,4)

But, converted to Business Objects, it looks like:

= If (
  (
    Truncate(
      (
        DayNumberOfWeek(
          ToDate(
            FormatNumber(MonthNumberOfYear(<Date>), "##") & "/1/" & FormatNumber(Year(<Date>), "####"),
            "mm/dd/yyyy"
          )
        ) + DayNumberOfMonth(<Date>) - 2
      ) / 7,
      0
    ) + 1
  )
  > 4
)
Then 4
Else (
  Truncate(
    (
      DayNumberOfWeek(
        ToDate(
          FormatNumber(MonthNumberOfYear(<Date>), "##") & "/1/" & FormatNumber(Year(<Date>), "####"),
          "mm/dd/yyyy"
        )
      ) + DayNumberOfMonth(<Date>) - 2
    ) / 7,
    0
  ) + 1
)