Weekend date with set values

**bezza** · 01-09-2013, 08:22 AM

Hi

I am trying to pivot source data and need the results in a weekending format. I have inserted a new column next to the date and i need a formula which puts the date into a weekending.

My date is in C2, i need all days Thursday through to Wedndesday to show as the Wednesday weekending, for exmaple Thursday August 5th 2012 through to Wednesday September 5th needs to show as week ending September 5th. Then Thursday 6th through to Wednesday 12th September will all show as weekending 12th September.

Can one formula work a whole years worth and can you help?

Thanks

Bezza

**Ace_XL** · 01-09-2013, 08:29 AM

Try this..

Please Login or Register  to view this content.

**sdsurzh** · 01-09-2013, 08:33 AM

Hi,

Are you looking the below kind of dates?
Dates Days
10-Jan-13 Thursday
17-Jan-13 Thursday
24-Jan-13 Thursday
31-Jan-13 Thursday
07-Feb-13 Thursday
14-Feb-13 Thursday
21-Feb-13 Thursday

[Formula =WORKDAY(C2,5) ]

Thanks,
SK

**MarvinP** · 01-09-2013, 08:35 AM

Hi bezza and welcome to the forum,

If you are really using a Pivot Table, then you can group your data and select the number of days in the grouping. If the first date is a Sunday and you group by 7 days then you will get a week ending or weekend grouping. See http://www.contextures.com/xlPivot07.html for more specifics and examples. See the attached for an example.