Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

CONCATENATE with dates

0

Hello everyone. I'm new heare and have a question. I am making a spreadsheet and using data from it to populate another sheet with the CONCATENATE function. I have it working but the date founction has be baffeled. I have searcged this and find what is suppose to work but it doesnt. I just get errors. I just want the date to show mm/dd/yyyy in the cell where I CONCATENATE the data.

This is the line I am using where O3 is the date being pulled.

=CONCATENATE(mainsheet!D3,"-",mainsheet!F3,"-",mainsheet!L3,"-",mainsheet!O3,"-",mainsheet!Q3)

Thanks for any help. BTW I'm using MS excel 2007 and also have open office.

excel
MS Office Version: 2007
posted by: PaulMin on Sat Aug 5, 2017 at 4:10 pm
Answer
Discuss

Answers

0

Unless you have expressly formatted a cell as Text Excel stores dates a numbers. These numbers will be displayed as dates. Unless you have specifically formatted a cell to show another format Excel will use the Short Date format specified in the Regional Settings.

The CONCATENATE function doesn't apply any format automatically. Therefore, if the referenced cells contain proper dates (meaning numbers) you will concatenate numbers. However, you can format those numbers within the CONCATENATE function. For example,

=CONCATENATE(Text(mainsheet!D3,"dd/mm/yyyy"),"-",Text(mainsheet!F3,"dd/mm/yyyy"),"-",Text(mainsheet!L3,"dd/mm/yyyy"),"-",Text(mainsheet!O3,"dd/mm/yyyy"),"-",Text(mainsheet!Q3,"dd/mm/yyyy"))

Then, if you wish to be perfect, you might include the hyphen in the date format:

=CONCATENATE(Text(mainsheet!D3,"dd/mm/yyyy-"),Text(mainsheet!F3,"dd/mm/yyyy-"),Text(mainsheet!L3,"dd/mm/yyyy-"),Text(mainsheet!O3,"dd/mm/yyyy-"),Text(mainsheet!Q3,"dd/mm/yyyy"))
posted by: Variatus (rep: 4889) on Sat Aug 5, 2017 at 8:59 pm
Discuss

Discussion

Yes this is what I've been doing and ll I get is errors. I just tried your exampple and get the error. Here is the cell entry.
CONCATENATE(mainsheet!D3,"-",mainsheet!F3,"-",mainsheet!L3,"-",mainsheet!O3,"dd/mm/yyyy"),"-",mainsheet!Q3)
PaulMin Aug 5, '17 at 9:16 pm
Which of the referenced cells contain dates? Which is the error you are getting? The formula you quote above isn't the one I supplied. The last item should be 
Text(mainsheet!O3,"dd/mm/yyyy")
. Test this formula outside the Concatenation to be sure you have it correct.
Variatus (rep: 4889) Aug 5, '17 at 9:22 pm
The cell with the Date reference is O3 of the mainsheet. It is formated as a date field. The error I get is that there is an error in the formula.
I guess I'm loosing this. I tried Text(mainsheet!O3,"dd/mm/yyyy") in a cell outside of the Concatenation and it returns just the string of the formula. 
PaulMin Aug 5, '17 at 10:14 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login