year of the date

[mark692]

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

Excel 2013/2016
	A	B	C
1	Date	Group Code	Year
2	03/17/2018	1	2018
3		1	2018
4		1	2018
5	03/06/2017	2	2016, 2017
6	01/01/2016	2	2016, 2017
7		2	2016, 2017
8	12/01/2017	3	2017
9		3	2017
Sheet1

 

[Dossfm0q]

Try Now please

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
    MyYear = Year(Cells(R, "A").Value)
    If InStr(GroupDates, MyYear) = 0 Then GroupDates = GroupDates & ", " & MyYear
    End If
  Next
  GroupDates = Mid(GroupDates, 3)
  Application.Volatile
End Function

 

[mark692]

Try Now please

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
    MyYear = Year(Cells(R, "A").Value)
    If InStr(GroupDates, MyYear) = 0 Then GroupDates = GroupDates & ", " & MyYear
    End If
  Next
  GroupDates = Mid(GroupDates, 3)
  Application.Volatile
End Function

hmm im confuse the first time i apply your code on my workbook it works but now its not.. hmmm

 

[mark692]

i hope im not asking too much can you check the if i inserted the code properly?

http://www.mediafire.com/file/oce2933i3fibbin/Sales.xlsm


Column A= date
Column P= Group Code
Column S= is suppose to be where the year will appear

Module9= were you can find the code and i modified it a little to work on my worksheet at first it work i but now dont know what happened, its shows "#VALUE!" now

thanks for all your answer!

 

[Rick Rothstein]

i hope im not asking too much can you check the if i inserted the code properly?

http://www.mediafire.com/file/oce2933i3fibbin/Sales.xlsm


Column A= date
Column P= Group Code
Column S= is suppose to be where the year will appear

Module9= were you can find the code and i modified it a little to work on my worksheet at first it work i but now dont know what happened, its shows "#VALUE!" now

Try replacing the code you have in Module9 with this code...

[table="width: 500"]
[tr]
	[td]Function GroupDates(GroupNum As Long) As String
  Dim R As Long, Groups As Variant
  Groups = Range(Cells(1, "P"), Cells(Rows.Count, "P").End(xlUp))
  For R = UBound(Groups) To 6 Step -1
    If Cells(R, "P").Value = GroupNum And Cells(R, "A") <> "" Then
      If Not GroupDates & ", " Like "*" & Year(Cells(R, "A").Value) & "*" Then
        GroupDates = GroupDates & ", " & Year(Cells(R, "A").Value)
      End If
    End If
  Next
  GroupDates = Mid(GroupDates, 3)
End Function[/td]
[/tr]
[/table]

Then go to your "Collection Check" sheet and refresh it... once you do that, all of the values in Column S should correct themselves.

 

[mark692]

Try replacing the code you have in Module9 with this code...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Function GroupDates(GroupNum As Long) As String
  Dim R As Long, Groups As Variant
  Groups = Range(Cells(1, "P"), Cells(Rows.Count, "P").End(xlUp))
  For R = UBound(Groups) To 6 Step -1
    If Cells(R, "P").Value = GroupNum And Cells(R, "A") <> "" Then
      If Not GroupDates & ", " Like "*" & Year(Cells(R, "A").Value) & "*" Then
        GroupDates = GroupDates & ", " & Year(Cells(R, "A").Value)
      End If
    End If
  Next
  GroupDates = Mid(GroupDates, 3)
End Function[/TD]
[/TR]
</tbody>[/TABLE]

Then go to your "Collection Check" sheet and refresh it... once you do that, all of the values in Column S should correct themselves.

thanks you so much sir, it works!