0

Dates Formula

Dom Oldham

February 16, 2023 14:55

None

Follow

Hi, can anyone help? I've inherited maintenance of of Klips. Can anyone help me with what the below is actually doing? Thanks

SLICE(
    REPEAT(
        SLICE(
           @G:G;,
            DATE_UNITVALUE(TODAY(), "month", "oct") - 2,
            DATE_UNITVALUE(TODAY(), "month", "oct") - 1
        ) / COUNT_DAYS(
            DATE_STARTOF(TODAY(), "month"),
            DATE_ENDOF(TODAY(), "month")
        ),
        COUNT_DAYS(
            DATE_STARTOF(TODAY(), "month"),
            DATE_ENDOF(TODAY(), "month")
        )
    ),
    0,
    COUNT_DAYS(
        DATE_STARTOF(TODAY(), "month"),
        TODAY()
    )
)

• Facebook
• Twitter
• LinkedIn

2 comments

• 
  Parker Selman February 16, 2023 16:19 Official comment

  Hi Dom! 
  
  Thanks for the post - I'll walk you through what this formula is doing below, working from the inside out:
  
  SLICE(
             @G:G;,
              DATE_UNITVALUE(TODAY(), "month", "oct") - 2,
              DATE_UNITVALUE(TODAY(), "month", "oct") - 1
          ) / COUNT_DAYS(
              DATE_STARTOF(TODAY(), "month"),
              DATE_ENDOF(TODAY(), "month")
  
  This section of the formula (which is used as the data to be repeated by the REPEAT formula), is returning one specific value in column G, determined by the number of the previous month in a year starting in October. executing this formula today would return the 4th value in your column, for example, as the previous month (January) is the 4th month in a year starting in October. 
  
  Then, this returned value is divided by the number of days in the current month. 
  
  Now, adding the REPEAT around the SLICE (with new parts bolded):

   REPEAT(
          SLICE(
             @G:G;,
              DATE_UNITVALUE(TODAY(), "month", "oct") - 2,
              DATE_UNITVALUE(TODAY(), "month", "oct") - 1
          ) / COUNT_DAYS(
              DATE_STARTOF(TODAY(), "month"),
              DATE_ENDOF(TODAY(), "month")
          ),
          COUNT_DAYS(
              DATE_STARTOF(TODAY(), "month"),
              DATE_ENDOF(TODAY(), "month")
          )
  
    This repeats the previously calculated value n times, where n is the number of days in the month.
  
  Then,
  
  SLICE(
      REPEAT(
          SLICE(
             @G:G;,
              DATE_UNITVALUE(TODAY(), "month", "oct") - 2,
              DATE_UNITVALUE(TODAY(), "month", "oct") - 1
          ) / COUNT_DAYS(
              DATE_STARTOF(TODAY(), "month"),
              DATE_ENDOF(TODAY(), "month")
          ),
          COUNT_DAYS(
              DATE_STARTOF(TODAY(), "month"),
              DATE_ENDOF(TODAY(), "month")
          )
      ),
      0,
      COUNT_DAYS(
          DATE_STARTOF(TODAY(), "month"),
          TODAY()
      )
  )
  
  Finally, the formula then slices the data further by only returning the values that fall within the first n rows, where n is the number of days between the start of this month and today. I do want to note that this formula could be simplified by removing this section entirely and instead having the REPEAT function only repeat the values for each day between the start of the month and today, rather than repeating it for the whole month and then trimming it down.
  
  I hope this helps! Please let us know if you have any further questions around this by submitting a ticket to support@klipfolio.com

  
  Best,
  
  Parker

• 0
  
  Dom Oldham February 16, 2023 16:38

  Hi, thanks for this.

   

  Much appreciated.