7 Replies Latest reply on Aug 6, 2018 6:33 PM by Boreak Silk

    Comparing sales across multiple years based on time period determined by start date and end date in parameter

    Boreak Silk

      Hi All,

      I’m trying to answer a simple question – how are my sales during a time period, determined
      by start and end date in the parameter, compared across multiple years. For
      example, when I select start date 1 Jan 2018 and end date 30 Jan 2018, I wanted
      to see sales for 1-30 Jan in 2018, 2017, 2016, …

       

      I created below formula and it works if the start date and end dates are in the same
      year.

       

       

      SUM(IF (MONTH([Date])*100 + DAY([Date]))>= (MONTH([Start date])*100 + DAY([Start date]))

      AND

      (MONTH([Date])*100 + DAY([Date])) <=(MONTH([End date])*100 + DAY([End date]))

      THEN [Sales]

      END)

       

      But if start and end dates are in different years, it does not work. Any idea how
      to modify this formula to give the right answer if the start date and end date
      are in different years? For example, something that works when I select 30 Dec
      2017 and 30 Jan 2018? Please find attached workbook.

       

      Thank in advance for any assistance.

       

      Kind regards