Excel How to return dates that fall within a year

Question

I have a sheet [Sheet1] which contains location and dates

Column A contains locations and Columns B to BT contains dates which ranges from years 2013 to 2023

  |     A    |    B     |    C     |    D        ~   |    BT    |
1 |   Loc1   |  (date)  |  (date)  |  (date)         |  (date)  |
2 |   Loc2   |  (date)  |  (date)  |  (date)         |  (date)  |
3 |   Loc3   |  (date)  |  (date)  |  (date)         |  (date)  |
~
46|   Loc46  |  (date)  |  (date)  |  (date)         |  (date)  |

I need Sheet2 to summarize Sheet1 for this year (2017)

Column A, still have locations and on Columns B and C return the dates which fall within this year 2017

  |     A    |    B     |    C     |
1 |   Loc1   |  (date)  |  (date)  |
2 |   Loc2   |  (date)  |  (date)  |
3 |   Loc3   |  (date)  |  (date)  |
~
46|   Loc46  |  (date)  |  (date)  |

There are just mostly two dates in each row that fall within the same year

I need to summarize until 2023 on different sheets

The dates may change from time to time so I can't copy paste to summarize

I have tried index and match but I don't know how to address dates

Thank you in advance

leruma leruma · Accepted Answer · 2017-04-25T06:04:37

So I already got the solution. Feel free to copy if you happen to have the same problem.

=IFERROR(SMALL(IF(YEAR(INDEX(Sheet1!$B$2:$BT$6,MATCH($A2,Sheet1!$A$2:$A$6,0),))=$A$1,INDEX(Sheet1!$B$2:$BT$6,MATCH($A2,Sheet1!$A$2:$A$6,0),)),COLUMNS($B:B)),"")

Excel How to return dates that fall within a year

1 Answers