Date & Time functions

MTHS_OFFSET

About

Finds values from a values vector, offset by a positive or negative number of months. E.g. for prior quarter, use a months_offset value of -3

Inputs:

  • months_offset : no. of months to offset by
  • dates_vector : vector of dates (should be end of month dates)
  • values_vector : vector of values (should be same size and shape as dates_vector)

Code

M.S. Excel 
MTHS_OFFSET = LAMBDA(months_offset, dates_vector, values_vector,
    LET(
        offset_dates_vector, DATE(
            YEAR(dates_vector),
            MONTH(dates_vector) + months_offset + 1,
            0
        ),
        offset_values_vector, XLOOKUP(
            offset_dates_vector,
            dates_vector,
            values_vector,
            "-",
            0,
            1
        ),
        offset_values_vector
    )
);