Skip to Content
0

Dates in table column header

Dec 12, 2016 at 08:50 PM

106

avatar image

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
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Mahboob Mohammed Dec 12, 2016 at 10:05 PM
1

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

Share
10 |10000 characters needed characters left characters exceeded
Zac Williams Dec 13, 2016 at 03:02 PM
0

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!

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Glad, I was able to help, Zac!

0