Merging Multiple Cells With A Date

[Los7uk]

Hello peeps. First post so please go easy on me.
I need some help with a certain database import i am doing, ill keep it short and sweet. I'll start with what i need to do, then i will tell you what problem i have.

First, what i need to do is, i need to merge data from multiple cells (in a row) into one cell separated by colons and semi colons. This part i have pretty much nailed down. The problem comes when it takes the date. The date in the cell looks like 01/01/1970. But when its taken into the merged cell, it looks like numbers (48272, or something like that) what i originally wanted to do was change the date to look like this 01-01-1970 (dashes not slashes) by using find and replace, but that didnt work. So in a nutshell, i need to be able to merge a date as it looks in a cell, then change the slashes to dashes as the import software for some reason doesnt like slashes. I hope this makes sense

Thanks in advance

 

[MickG]

For Dates in Range "B1:F1", results in "A1"

Dim str As String, R As Range
For Each R In Range("B1").Resize(, 5)
   str = str & "; " & Format(R, "dd-mm-yyyy")
Next R
Range("A1").Value = Mid(str, 2)

 

[Los7uk]

Hello, Thanks for the quick reply. I have to be honest, i have no idea what that means I dont know excel very well when it comes to macros, formulas etc. I don't mean that to be rude and i appreciate your help. I would add an example of the spreadsheet but it wont let me. I would rather do it without macros/VBA etc. Just something simple.

For argument sake, i will try and add an example here (not sure how the formatting will work so wish me luck)

The whole aim of this task is to get this = "Last Serviced:08-02-2013" <--- exactly like this without quotes

This is what i am trying

G1= Last serviced G2 = 08/02/2013

I use a formula like this =(G1&":"&H1) I would expect to get "Last Serviced:08/02/2013" but i get this "Last Serviced:413113"

so firstly, how do i get it to show the date, secondly, how would i then change the slashes to dashes?

p.s I know that once i have run the formula, i need to edit copy, then "paste values" to get the actual data in the cells rather than just the formula

I thank you once again in advance and hope you can help this noob

 

[vmjan02]

can u be more clear as what you need.

 

[MickG]

ADVERTISEMENT

Perhaps something like this:-

=G1& ": " &TEXT(H1,"dd-mm-yyyy ")&", "&TEXT(I1,"dd-mm-yyyy")

 

[Los7uk]

Hello MickG,

Thanks again for the super fast reply. Your formula kind of worked (maybe i didn't explain properly) but from your code i managed to tweak/delete a few things to get exactly what i need, so thank you very much for pointing me in the right direction. The formula that works for me is =G1& ":" &TEXT(H1,"dd-mm-yyyy")

Again thank you very much

Steve

 

[MickG]

Glad its working.