Convert filter output to date

sharshra

Active Member
Joined
Mar 20, 2013
Messages
302
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

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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"))
 
Upvote 0
Solution
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,218,514
Messages
6,142,910
Members
450,453
Latest member
spaudel

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
Back
Top