cancel
Showing results for 
Search instead for 
Did you mean: 

Dates in table column header

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

mhmohammed
Active Contributor

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

Answers (1)

Answers (1)

Former Member
0 Kudos

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!

mhmohammed
Active Contributor
0 Kudos

Glad, I was able to help, Zac!