Date function

[shezz]

Hi all, i have data and i want some results automatically in a specific cell kindly help me i am posting an example of data here.[TABLE="width: 800"]
<tbody>[TR]
[TD]Target achievers name[/TD]
[TD]target achieved date[/TD]
[TD]i want here when i enter name of target achiever i.e[/TD]
[TD]Result column[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]12-9-2017[/TD]
[TD]Paul[/TD]
[TD]all of dates on which Paul got target come together, 12-9-2017, 14-9-2017 and 16-9-2017[/TD]
[/TR]
[TR]
[TD]Smith[/TD]
[TD]13-9-2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]14-9-2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jennifer[/TD]
[TD]15-9-2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]16-9-2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Re: Date function, kindly help.

Give this event code a try...

[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Cell As Range, Result As String
  On Error GoTo NothingThere
  If Not Intersect(Target, Columns("C")) Is Nothing Then
    For Each Cell In Columns("A").SpecialCells(xlConstants)
      If Cell.Value = Target.Value Then Result = Result & ", " & Application.Text(Cell.Offset(, 1).Value, Cell.Offset(, 1).NumberFormat)
    Next
    Result = Mid(Result, 3)
    If InStr(Result, ",") Then Result = Application.Replace(Result, InStrRev(Result, ","), 1, " and ")
    Target.Offset(, 1).NumberFormat = Array("@", "General")(-(Target.Value = ""))
    Target.Offset(, 1) = Result
  End If
NothingThere:
End Sub[/td]
[/tr]
[/table]

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... 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.

 

[Peter_SSs]

Re: Date function, kindly help.

In case you have Excel through Office 365 and are looking for a formula solution, you could consider this. It is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}.

Book1
	A	B	C	D
2	Paul	12/09/2017	Paul	12-9-2017, 14-9-2017, 16-9-2017
3	Smith	13/09/2017
4	Paul	14/09/2017
5	Jennifer	15/09/2017
6	Paul	16/09/2017
Textjoin

Cell Formulas
Range	Formula
D2	{=TEXTJOIN(", ",1,IF(A2:A6=C2,TEXT(B2:B6,"d-m-yyyy"),""))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Rick Rothstein]

Re: Date function, kindly help.

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]{=TEXTJOIN(", ",1,IF(A2:A6=C2,TEXT(B2:B6,"d-m-yyyy"),""))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

What about the word "and" that the OP indicated he wanted between the last two found dates in his output?

 

[Peter_SSs]

Re: Date function, kindly help.

What about the word "and" that the OP indicated he wanted between the last two found dates in his output?

I'll adjust if asked by the OP, but I'm thinking that they may be just interested in the dates.