I am trying to combine a date cell with a general cell.
Here is the data:
Date Code
14/03/19 HACK001
14/03/19 DUNN001
14/03/19 BRAD001
This is what I have used:
but the output is:
Please Login or Register to view this content.
Date Code Combined
14/03/19 HACK001 43538,HACK001
14/03/19 DUNN001 43538,DUNN001
14/03/19 BRAD001 43538,BRAD001
How can I make it to look like this:
Date Code Combined
14/03/19 HACK001 14/03/19,HACK001
14/03/19 DUNN001 14/03/19,DUNN001
14/03/19 BRAD001 14/03/19,BRAD001
Excel stores dates as a number, then displays them according to the cell numberformat.
To display as a text date, you need to convert the number to text:
Formula:
Please Login or Register to view this content.
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
That worked! - Thank you
try: =FormatFormula(A2) & "," & B2
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Not familiar with that function Alan - can't find any reference, and doesn't exist in Excel 2016...?
Guess it must be a 2019 function. Olly, thanks for noting.
I can't find any documentation on it? Are you sure it's native?
I don't have any add-ins and I am running 2019 and it appears to be native.
v A B C D E F 1 Date Code Combined 2 14/03/19 HACK001 43538,HACK001 3/14/2019,HACK001 3 14/03/19 DUNN001 43538,DUNN001 3/14/2019,DUNN001 4 14/03/19 BRAD001 43538,BRAD001 3/14/2019,BRAD001
v A B C D E F 1 Date Code Combined 2 43538 HACK001 =A2&","&B2 =FormatFormula(A2) & "," &B2 3 43538 DUNN001 =A3&","&B3 =FormatFormula(A3) & "," &B3 4 43538 BRAD001 =A4&","&B4 =FormatFormula(A4) & "," &B4
Attachment 659168 This is what is in 2019
Can't see your attachment. Seems odd that it's not documented? How did you find it?!
Here is the attachment again. I had not seen or used this previously. Thought I had read about it in a post in one of the forums a couple of months back. Details are really fuzzy. However, it is not part of my UDF's
Last edited by alansidman; 01-23-2020 at 06:18 AM.
Doesn't look native - Lower case function name, and those argument names don't look standard Microsoft.
I'm intrigued - wondering where it's come from, and what it does!
That is UDF from ForumTools AddOn.
Capture.JPG
Happy with my answer * Add Reputation.
If You are happy with solution, please use Thread tools and mark thread as SOLVED.
@KOKOSEK
Interesting. I don't recall adding ForumTools AddOn to my system, but it is clearly, in my VBE. Thanks for clarifying.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks