How to display dates in a week in a Pivot Table

[#1]

Hi,

I had to offset a week to suit a particular buying week.

I now need to display the date range contained in the week in a pivot table.

For example, Week 21 contains: 01/04/2013 - 07/04/2013

How do I display the dates for this week, I will have to display the last 3 weeks figures etc.

I have tried lunarweek and weekstart functions but I cant get it working.

Cheers,

Gregg

[Anonymous]

I would use either

weekstart (Date) &'-'& weekend(Date)

or

Date &'-'& date( Date -7)

[#3]

Hi,

I need to pass in the new week that I created as the Date field is separate.

The new week = BR and contains 7 dates

If I use the formula below it uses the bultin calendar week Monday – Sunday

I need to use Friday – Thursday

Regards,

  Gregg

[#4 · Accepted solution]

Hi,

I used the weekstart and weekend function and got it working.

Using the Today() function which denotes week (a bit confusing but it works)

WeekStart(Today(),-1,-10)& ' -  ' &WeekEnd(Today(),-1,+4) as *********

Cheers,

Gregg

[rustyfishbones]

Hi Gregg,

Let me know if this works

    DUAL(PICK(WEEKDAY(Date) + 1, 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun'), WeekDay(Date - 3)) AS ThuFriWeekDay,

     Week(Date-3) AS ThuFriWeekNo,