0

I have a spreadsheet as follows in column B:

10/11
27/10
05/05
01/01
24/12
11/07
03/03
27/11
15/10

The top ones are always from the year that we are, in this example it's 2019, but according to what goes back to 24/12 means that we are going to December 2018, going down a little bit and getting to 27/11 means that we are going to November 2017.

I would like to know if there is any formula that could make this distinction in the year and add it in Column A, because the data I import from always updates and comes with only day and month.

Expected Result:

2019   10/11
2019   27/10
2019   05/05
2019   01/01
2018   24/12
2018   11/07
2018   03/03
2017   27/11
2017   15/10

Link to Spreadsheet:

https://docs.google.com/spreadsheets/d/1tXxPdZMjkqIUPKlJt9j8fBdgMfRTH6rU1BeGpHW-m6Y/edit?usp=sharing

  • Just to clarify: if a cell displays "10/11", then you intend that first number (10) represents the day and the second number (11) represents the month. So, "10/11" would represent 10 November. – Tedinoz Nov 14 at 7:31
0

You want to display the relevant year that matches a Day/Month value in Column B that is understood to be sorted in descending order.

The format of the Day/Month values are not consistent. Some values are dates while others are strings. This is confirmed by isdate().

There are two formulas:

Cell A1:

  • =if(row()=1,2019,1)

Cell A2, copy to bottom of page:

  • =if(row()=1,2019,if(index(SPLIT(to_text(B1), "/"), 0, 2)=index(SPLIT(to_text(B2), "/"), 0, 2),A1,if(index(SPLIT(to_text(B1), "/"), 0, 2)>index(SPLIT(to_text(B2), "/"), 0, 2),A1,A1-1)))

The formula in Cell A2 deserves explanation:

  • if(row()=1,2019

    • if this is row 1, then the year = 2019, otherwise...
  • to_text(B1)

    • converts the Day/Month value in the preceding row to a string; it doesn't matter whether the value was a date or a string, it will be converted to a string.
  • index(SPLIT(to_text(B1), "/"), 0, 2)
    • `splits the string in the preceding row and returns the second value as a number value - this represents the month of the year.
  • if(index(SPLIT(to_text(B1), "/"), 0, 2)=index(SPLIT(to_text(B2), "/"), 0, 2),A1
    • tests if the month value in the preceding row equals the month value of this row
    • if yes, then the year is the same as the preceding row, otherwise...
    • if no, then the following formula applies...
  • if(index(SPLIT(to_text(B1), "/"), 0, 2)>index(SPLIT(to_text(B2), "/"), 0, 2),A1,A1-1
    • tests if the month value in the preceding row is greater than the month value of this row,
    • if yes, then the year is the same as the preceding row,
    • if no, then the preceding month must be less than the current month. Since the dates are sorted in descending order, this can only mean that a new year has begin, so the value of the year for this row is the preceding value minus one.

To do:

  • Insert the relevant formula in Cell A1 and in Cell A2
  • Copy the formula in Cell A2 to the bottom row of Column A
  • Select all the cells in column A (Cell A1 to the bottom of the sheet) > Copy > Paste Values.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.