Skip to Content
avatar image
Former Member

Dates in table column header

Hello everyone, there is something I’m trying to do involving dates that I have not been able to successfully create.

I am making a report that will be run once weekly. In the report there will be six columns where I’m pulling in weekly amounts of a metric for each of the following six weeks. I want to make headers for the columns that read “Week of xx/xx/xxxx”. The trick is that I need those headers to reflect the dates of the next six Mondays, regardless of when the report is run.

For now, as a work-around, I’m planning to just manually run the report every Friday, and have created six variables to read into the column headers that just count 3, 10, 17, 24, 31, and 38 days from the day the report is run. For example, for the first column –

=”Week Of ” +RelativeDate(CurrentDate();3)

This is working just fine, given of course that I only ever run the report on a Friday. Can anyone give me any pointers on how I can set it up to always read in the dates for all six following Mondays, regardless of the day on which the report is run?

Thank you!

– Zac

Reply
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Dec 12, 2016 at 10:05 PM

    Hi Zac,

    Create the below variables:

    v_CurrDt = CurrentDate()

    v_DayNmofCurrDt = Dayname([v_CurrDt])

    v_1stMondayaftertoday = If([v_DayNmofCurrDt]="Tuesday") Then RelativeDate([v_CurrDt];6)

    ElseIf([v_DayNmofCurrDt]="Wednesday") Then RelativeDate([v_CurrDt];5)

    ElseIf([v_DayNmofCurrDt]="Thursday") Then RelativeDate([v_CurrDt];4)

    ElseIf([v_DayNmofCurrDt]="Friday") Then RelativeDate([v_CurrDt];3)

    ElseIf([v_DayNmofCurrDt]="Saturday") Then RelativeDate([v_CurrDt];2)

    ElseIf([v_DayNmofCurrDt]="Sunday") Then RelativeDate([v_CurrDt];1)

    v_2ndMondayaftertoday = RelativeDate([v_1stMondayaftertoday];1;WeekPeriod)

    OR

    = RelativeDate([v_1stMondayaftertoday];7)

    (You can use the parameter WeekPeriod, if you have BO version 4.1 SP6 or later.)

    Similarly, you can create variables for the following 3rd, 4th, 5th, 6th Mondays.

    Hope that helps.

    Thanks,

    Mahboob Mohammed

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 13, 2016 at 03:02 PM

    This is exactly what I needed! And I understand the logic so I can do permutations of this at a later point. Thank you Mahboob!

    Add comment
    10|10000 characters needed characters exceeded