3 Replies Latest reply: Aug 12, 2014 9:01 AM by Jamie Gardner RSS

    Split weeks into two datasets if month changes

      I have a dataset over 6 months which I need to split out into a weekly view. However, when the month changes during the week, I need to be able to show the data up to the end of the month and then for the remainder of the week, for example:

       

      FROM

      20-Jul-14 = 642

      27-Jul-14 = 498

      03-Aug-14 = 572

       

      TO

      20-Jul-14 = 642

      27-Jul-14 = 498

      30-Jul-14 = 403

      01 to 03-Aug-14 = 169

       

      I would like to do this in a straight table or pivot table. It must be some combination of the WeekEnd and MonthEnd functions, but I just can't seem to replicate what I need. I have attached an image from the spreadsheet I am trying to replicate

       

      WeekMonth.bmp