year of the date

mark692

Active Member
Joined
Feb 27, 2015
Messages
299
Office Version
  1. 2016
Platform
  1. Windows
i want to put a formula on column C that will show me the year of the given date on column A inside the same group code in column B, like this

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Date</td><td style=";">Group Code</td><td style=";">Year</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">03/17/2018</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2018</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">2018</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">2018</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">03/06/2017</td><td style="text-align: right;;">2</td><td style=";">2016, 2017</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">01/01/2016</td><td style="text-align: right;;">2</td><td style=";">2016, 2017</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;">2</td><td style=";">2016, 2017</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">12/01/2017</td><td style="text-align: right;;">3</td><td style="text-align: right;;">2017</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td><td style="text-align: right;;">2017</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br />
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,314
Office Version
  1. 2010
Platform
  1. Windows
Does each code group always occupy exactly 3 rows as shown in your example?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,314
Office Version
  1. 2010
Platform
  1. Windows
See if this UDF (user defined function) does what you want...
Code:
Function GroupDates(GroupNum As Long) As String
  Dim R As Long, Dates As Variant
  Dates = Range(Cells(1, "B"), Cells(Rows.Count, "B").End(xlUp))
  For R = UBound(Dates) To 1 Step -1
    If Cells(R, "B").Value = GroupNum And Cells(R, "A") <> "" Then
      GroupDates = GroupDates & ", " & Year(Cells(R, "A").Value)
    End If
  Next
  GroupDates = Mid(GroupDates, 3)
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use GroupDates just like it was a built-in Excel function. For example,

=GroupDates(B2)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
 

Nishant94

Well-known Member
Joined
May 8, 2015
Messages
510
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

If you have Excel 2016, you can use this or UDF is the way to go for you as given by Rick Rothstein :

<b>Unknown</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Date</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Group Code</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Year</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">03/17/2018</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2018</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2018</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2018</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">03/06/2017</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2017,2016</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">01/01/2016</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2017,2016</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2017,2016</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">12/01/2017</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2017</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2017</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet13</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C2</th><td style="text-align:left">{=TEXTJOIN(<font color="#0000FF">",",,IF(<font color="#FF0000">(<font color="#00FF00">$B$2:$B$9=B2</font>)*(<font color="#00FF00">$A$2:$A$9<>""</font>),YEAR(<font color="#00FF00">$A$2:$A$9</font>),""</font>)</font>)}</td></tr></tbody></table><b>Enter the formula with "Ctrl+Shift+Enter". Only Pressing "Enter" will result in formula giving error or incorrect result. </b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 
Last edited:

mark692

Active Member
Joined
Feb 27, 2015
Messages
299
Office Version
  1. 2016
Platform
  1. Windows
If you have Excel 2016, you can use this or UDF is the way to go for you as given by Rick Rothstein :

Unknown
ABC
1DateGroup CodeYear
203/17/201812018
312018
412018
503/06/201722017,2016
601/01/201622017,2016
722017,2016
812/01/201732017
932017
10

<tbody>
</tbody>
Sheet13

Array Formulas
CellFormula
C2{=TEXTJOIN(",",,IF(($B$2:$B$9=B2)*($A$2:$A$9<>""),YEAR($A$2:$A$9),""))}

<tbody>
</tbody>
Enter the formula with "Ctrl+Shift+Enter". Only Pressing "Enter" will result in formula giving error or incorrect result. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Hi im using my your formula bat it gives me result like this yes im using 2016 thanks

ABC
1dategroupyear
201/21/20181#NAME?
301/21/20171#NAME?
41#NAME?
502/21/20172#NAME?
602/21/20172#NAME?

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Array Formulas
CellFormula
C2{=TEXTJOIN(",",,IF(($B$2:$B$6=B2)*($A$2:$A$6<>""),YEAR($A$2:$A$6),""))}
C3{=TEXTJOIN(",",,IF(($B$2:$B$6=B3)*($A$2:$A$6<>""),YEAR($A$2:$A$6),""))}
C4{=TEXTJOIN(",",,IF(($B$2:$B$6=B4)*($A$2:$A$6<>""),YEAR($A$2:$A$6),""))}
C5{=TEXTJOIN(",",,IF(($B$2:$B$6=B5)*($A$2:$A$6<>""),YEAR($A$2:$A$6),""))}
C6{=TEXTJOIN(",",,IF(($B$2:$B$6=B6)*($A$2:$A$6<>""),YEAR($A$2:$A$6),""))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 
Last edited:

mark692

Active Member
Joined
Feb 27, 2015
Messages
299
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

See if this UDF (user defined function) does what you want...
Code:
Function GroupDates(GroupNum As Long) As String
  Dim R As Long, Dates As Variant
  Dates = Range(Cells(1, "B"), Cells(Rows.Count, "B").End(xlUp))
  For R = UBound(Dates) To 1 Step -1
    If Cells(R, "B").Value = GroupNum And Cells(R, "A") <> "" Then
      GroupDates = GroupDates & ", " & Year(Cells(R, "A").Value)
    End If
  Next
  GroupDates = Mid(GroupDates, 3)
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use GroupDates just like it was a built-in Excel function. For example,

=GroupDates(B2)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.


hello sir your formula works but when i tried to apply it on may real working workbook it gives me error
 

mark692

Active Member
Joined
Feb 27, 2015
Messages
299
Office Version
  1. 2016
Platform
  1. Windows
like this sir,

[URL="https://ibb.co/d8RsPR"][IMG]https://preview.ibb.co/bDgRjR/year_image.jpg[/URL][/IMG]


its gives me 2016,2016,2015,2015,2015

instead of 2016,2015 only

the group code is the column that colored yellow
 

Dossfm0q

Active Member
Joined
Mar 9, 2009
Messages
353
maybe you need to add: Application.Volatile before last Line for update Auto or Use F9

GroupDates = Mid(GroupDates, 3)
Application.Volatile
End Function
 

Watch MrExcel Video

Forum statistics

Threads
1,109,072
Messages
5,526,640
Members
409,713
Latest member
roman9980

This Week's Hot Topics

Top