Convert filter output to date

sharshra

Board Regular
Joined
Mar 20, 2013
Messages
225
Office Version
  1. 365
I'm using textjoin & filter functions to get the dates when an account appears in the report. Output dates are in number format, but I need it in dd-mmm-yy format. I have done the formatting in dd-mmm-yy format, but still the output is in numbers (stored as text?). Any suggestions to fix this?

Formula in column F -
Excel Formula:
=TEXTJOIN(",",TRUE,FILTER($B$3:$B$18,$C$3:$C$18=$E5))

Note:
Data shown below is for illustration purpose only. Actual data runs into tens of thousands of rows & huge range of dates, accounts & other details.
Column F is formatted in dd-mmm-yy format.

(For some reason, XL2BB is not working. When I select the cells & click on mini sheet, I get a confirmation message saying that mini sheet is copied to clipboard successfully. But, when I paste in the post, nothing is getting pasted. Tried this several times, but didn't work. Hence, pasting the image & formula.)
1640776669429.png
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,284
Office Version
  1. 365
Platform
  1. Windows
Formula in column F -
Excel Formula:
=TEXTJOIN(",",TRUE,FILTER($B$3:$B$18,$C$3:$C$18=$E5))
Try instead

Excel Formula:
=TEXTJOIN(",",TRUE,TEXT(FILTER($B$3:$B$18,$C$3:$C$18=$E5),"dd-mmm-yy"))
 
Solution

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,284
Office Version
  1. 365
Platform
  1. Windows
You're welcome.
When I select the cells & click on mini sheet, I get a confirmation message saying that mini sheet is copied to clipboard successfully. But, when I paste in the post, nothing is getting pasted.
Nothing at all gets pasted?

What happens if you Paste into, say, Notepad instead? Do you get any code pasted?
 

sharshra

Board Regular
Joined
Mar 20, 2013
Messages
225
Office Version
  1. 365
It is working now :unsure:, but nothing different was done. Followed the same procedure - select the range, click on mini sheet, click OK in the message box & paste in this forum.

It happened before also - working & not working sometimes. When it doesn't work, it doesn't paste in anything in any application (notepad, word, ppt etc).
date issue.xlsx
BCDEF
2DateAccountUnique accountDates
301-Dec-2112345123451-Dec-21,2-Dec-21,3-Dec-21
401-Dec-2123456234561-Dec-21,3-Dec-21
501-Dec-2134567345671-Dec-21,3-Dec-21
601-Dec-2145678456781-Dec-21,3-Dec-21
701-Dec-2156789567891-Dec-21,2-Dec-21
802-Dec-2112345678902-Dec-21,3-Dec-21
902-Dec-2156789789013-Dec-21
1002-Dec-2167890890123-Dec-21
1103-Dec-2178901901233-Dec-21
1203-Dec-2189012
1303-Dec-2112345
1403-Dec-2190123
1503-Dec-2134567
1603-Dec-2145678
1703-Dec-2167890
1803-Dec-2123456
Sheet1
Cell Formulas
RangeFormula
E3:E11E3=UNIQUE(C3:C18)
F3:F11F3=TEXTJOIN(",",TRUE,TEXT(FILTER($B$3:$B$18,$C$3:$C$18=$E3),"d-mmm-yy"))
Dynamic array formulas.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,842
Messages
5,833,932
Members
430,245
Latest member
Girldad

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top