Add year (multiple different years) in cells that have only day and month (Google Sheets)

Question

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

Answer 1

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.