2 Replies Latest reply: Jul 12, 2013 6:21 AM by Friedrich Hofmann RSS

    Formula help - MonthsStart?

    Friedrich Hofmann

      Hi,

       

      a little background on what I want to do with this expression:

       

      I have on one worksheet two pivot_tables (looking like two calendar_sheets). Currently the logic is this:

      - Upon opening the sheet, the current month is automatically selected.

      - The upper calendar_sheet then displays the days (Mo-Sat) of the current month, with data from an underlying list in the corresp. field.

      - The lower one displays the same for the past month.

      - The problem that I'm just trying to avoid is that all this currently works only when a selection has been made - it is a condition for recalculation that exactly one
         month be selected. The formula in the lower diagram thus looks very different from the one in the upper diagram, making it very complicated to implement new
         features.

       

      After realizing that I cannot combine the MONTHSTART and MONTH() functions, I have tried MONTHsSTART. That seems to work, by trial-and-error I have found out how I can display the first and last week of this or the past month. I hope to implement this as a condition in the "week" dimension of both diagrams and thus get the same result without any selection.

      I would, however, like to understand what the function does - I have played around with the parameters and I can write either >MonthsStart(2, TODAY()), -1)< OR >MonthsStart(1, TODAY(), -2)<, both will give me the 1st of May (two months back).

      Can someone clarify this function for me, please?

       

      Thanks a lot!

      Best regards,

       

      DataNibbler