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

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
``````

• 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

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.