Question description:
This user has given permission to use the problem statement for this
blog.
Concat formula is incorrectly formatting the date in column C. Currently column B has dd/mm/yyyy and I require mm/dd/yyyy
Solved by Z. Y. in 31 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
15/10/2017 - 01:41
Welcome, thank you for choosing Got It Pro-Excel! I'll be glad to assist you with your concern.
User
15/10/2017 - 01:41
Hello
User
15/10/2017 - 01:41
I'm copying over sample data
Excelchat Expert
15/10/2017 - 01:42
I understand you need help with formatting the date using the concat formula.. may you please share the file you are referring to so I can help you with your concern, thanks!
User
15/10/2017 - 01:43
done - thanks
Excelchat Expert
15/10/2017 - 01:43
Can you share the file with the formula applied so we can fix it?
Excelchat Expert
15/10/2017 - 01:43
You may upload it here using the clip icon at the bottom of this chat
Excelchat Expert
15/10/2017 - 01:44
You said the concat formula is incorrectly formatting the date.. can you share the formula to help you? thanks
User
15/10/2017 - 01:45
=CONCATENATE(A2, " - ",B2(dd/mm/yy))
User
15/10/2017 - 01:45
so i need it MM/DD/YYYY
Excelchat Expert
15/10/2017 - 01:46
Ok, working on it now. Thanks
User
15/10/2017 - 01:46
thanks
Excelchat Expert
15/10/2017 - 01:51
Still fixing the dates, right now it is being formatted as text and not as DATE that's why the formula =CONCATENATE(A2, " - ",TEXT(B2,"MM/DD/YYYY")) doesn't work
Excelchat Expert
15/10/2017 - 01:51
thanks for your patience
User
15/10/2017 - 01:51
thanks!
Excelchat Expert
15/10/2017 - 01:52
Only B5 and B14 is correctly formatted as DATE
Excelchat Expert
15/10/2017 - 02:10
=IF(ISNUMBER(B2),CONCATENATE(A2," - ",TEXT(B2,"MM/DD/YYYY")),CONCATENATE(A2," - ",MID(B2,4,2),"/",LEFT(B2,2),"/",RIGHT(B2,4)))
Excelchat Expert
15/10/2017 - 02:10
that's the formula
Excelchat Expert
15/10/2017 - 02:10
Do you have any questions regarding the solution provided?
Excelchat Expert
15/10/2017 - 02:11
Here's the file with the formula
[Uploaded an Excel file]
User
15/10/2017 - 02:12
Thanks!
Excelchat Expert
15/10/2017 - 02:12
Is there anything else that I can assist you with regarding this issue?
User
15/10/2017 - 02:12
no looks good! thanks!
Excelchat Expert
15/10/2017 - 02:12
Thanks for using Got It Pro-Excel. Please give your kind feedback for our service. Have a good day! :)
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.