MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Help - concatenate date


Posted by bill on July 07, 2000 7:44 AM

I need to concatenate two columns. One of the column is in the format: dd/mm/yy
when i concatenate then the field displays the numeric value like :33445
Is there any way around this.

Thanks in advance


Posted by TT on July 10, 0100 4:36 AM


David
Alternatively, the formula per Mads¡¦ suggestion would be :-

=TEXT(B2,"mm/dd/yy") & " - " & TEXT(D2,"mm/dd/yy")

This formula has the advantage of being able to show the concatenated dates in whatever format is required.

TT

Posted by mike faulkner on July 07, 0100 10:10 AM

I've had this problem many times, and I have developed a somewhat awkward solution, but at least it works. The key is changing the date format to text but keeping the date intact.

If you know how to use VBA, here is what you can do. This code is to modify cell A1 that contains a date in the date format. You can modify the code to make it apply to your situation.

-Note- I use this in Excel 2000 - I don't know if it works in older versions.
----------
Sub date_to_text()

Range("a1").NumberFormat = "m/d/yy"
date_txt = range("a1").value
Range("a1").NumberFormat = "@"
Range("a1").Value = date_txt

End Sub

-Mike

Posted by mads on July 07, 0100 10:41 AM


Assuming your date is in cell A1, to return dd/mm/yy use the following in your formula :-

TEXT(A1,"dd/mm/yy")

mads

Posted by Ada on July 12, 0100 2:07 PM


Mike

Taking this one step further, the following macro will toggle the selected cells between date and text formats :-

Sub Toggle_Date_Text()
For Each cell In Selection
If cell.NumberFormat = "m/d/yy" Then
date_txt = cell.Value
cell.NumberFormat = "@"
cell.Value = date_txt
Else
txt_date = cell.Value
cell.NumberFormat = "m/d/yy"
cell.Value = txt_date
End If
Next
End Sub

Ada


Posted by David on July 09, 0100 8:31 PM

I had the same problem trying to concatenate two dates I did this.
I used MONTH(B2),"/",DAY(B2)

or my total formula was =CONCATENATE(MONTH(B2),"/",DAY(B2),"/00 - ",MONTH(D2),"/",DAY(D2),"/00")

Posted by David on July 10, 0100 9:48 PM

I like It. Thanks