Concatenation and dates in multiple formats

Topic

New Reply

I have a column with the dates in it; for a good reason the date is displayed in multiple formats. For instance, I have the following genuine items in my list. And, there are more formats!

11/23/04
January 1, 2004
1/3/2012
2013/04/05
07/08/2014
Oct 23, 2015

Is there ANY way I can concatenate this with a text cell and keep the formatting?

Reply | Quote

Replies

Reply | Quote

John,

This is a bit of a thorny problem as it matters how the date was originally entered Text vs Date or a format that Excel converted to a date.

Here’s a User Defined Function (UDF) that handles most of the stuff but as you can see it isn’t perfect as the Cell function on which it relies only has 9 recognized formats.

Code:

Option Explicit

Public Function FormatDateToText(vDate As Variant, zFMT As String) As String

  Dim vFormats(1 To 10, 1 To 2) As Variant
  Dim lCntr As Long
  
  'Application.Volatile
  
  vFormats(1, 1) = "D1": vFormats(1, 2) = "dd-mmm-yy"
  vFormats(2, 1) = "D2": vFormats(2, 2) = "ddd-mmm"
  vFormats(3, 1) = "D3": vFormats(3, 2) = "mmm-yy"
  vFormats(4, 1) = "D4": vFormats(4, 2) = "mm/dd/yy" '
  vFormats(5, 1) = "D5": vFormats(5, 2) = "mm/dd"
  vFormats(6, 1) = "D6": vFormats(6, 2) = "h:mm:ss AM/PM"
  vFormats(7, 1) = "D7": vFormats(7, 2) = "h:mm AM/PM"
  vFormats(8, 1) = "D8": vFormats(8, 2) = "h:mm:ss"
  vFormats(9, 1) = "D9": vFormats(9, 2) = "h:mm"
  vFormats(10, 1) = "G": vFormats(10, 2) = "General"
  
  For lCntr = 1 To UBound(vFormats)
  
     If vFormats(lCntr, 1) = zFMT Then
       
       If zFMT = "G" Then
         FormatDateToText = vDate
       Else
        FormatDateToText = Format(vDate, vFormats(lCntr, 2))
       End If
       
       Exit For
       
     Else
       FormatDateToText = ""
     End If
     
  Next lCntr
  
End Function 'FormatDateToText

Test Results:
46828-DateTextFmts

You’ll see I added a column for Spyware Doctors (SWD Method) solution, which works fine for text.

The UDF will handle both text and the limited number of date formats supported by the Cell function but as you can see in row 5 that has problems also.

Take note of the function calling sequence shown in the sample workbook screen shot or the file included below.

Hopefully someone has a comprehensive solution to your problem.

Test File: 46829-Excel-VBA-Concatenate-Text-to-Date-preserving-Date-format

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Well, I “sort of” solved the problem.
–I copied the entire column and then pasted it into Word. Word adds the cell contents as they are viewed, into a table.
–I then went back to Excel and set another (blank) column format as “text”. I put it next to my active date column so I could compare things.
–I then pasted the word table into the new column. I had to choose the proper Paste Special to get everything to enter the way I wanted, but it worked.

I for the most part really like Excel. But this is one of the times that I am willing to make an exception to that “like”.

Reply | Quote

Hi RG and John

It can be useful to work with cell contents ‘as displayed’ rather than what the cell actually contains (e.g. as shown in the formula bar).

So, a simpler function would be:
Function asDisplayed(cell)
asDisplayed = cell.Text
End Function

So, if your date was in say, cell [A5], regardless of format, and you wanted to concatenate it with text as in say, cell [D5],
your formula would just be be..
=asDisplayed(A5) & D5

etc etc etc.

zeddy

Reply | Quote

Hi RG

..so, to be clear, the vba..
cell.Text
..returns the contents ‘as displayed’, as opposed to
cell.Value
..which returns the ..well, you know what I mean.

zeddy

Reply | Quote

John,

This is a bit of a thorny problem as it matters how the date was originally entered Text vs Date or a format that Excel converted to a date.

Here’s a User Defined Function (UDF) that handles most of the stuff but as you can see it isn’t perfect as the Cell function on which it relies only has 9 recognized formats.

Code:

Option Explicit

Public Function FormatDateToText(vDate As Variant, zFMT As String) As String

  Dim vFormats(1 To 10, 1 To 2) As Variant
  Dim lCntr As Long
  
  'Application.Volatile
  
  vFormats(1, 1) = "D1": vFormats(1, 2) = "dd-mmm-yy"
  vFormats(2, 1) = "D2": vFormats(2, 2) = "ddd-mmm"
  vFormats(3, 1) = "D3": vFormats(3, 2) = "mmm-yy"
  vFormats(4, 1) = "D4": vFormats(4, 2) = "mm/dd/yy" '
  vFormats(5, 1) = "D5": vFormats(5, 2) = "mm/dd"
  vFormats(6, 1) = "D6": vFormats(6, 2) = "h:mm:ss AM/PM"
  vFormats(7, 1) = "D7": vFormats(7, 2) = "h:mm AM/PM"
  vFormats(8, 1) = "D8": vFormats(8, 2) = "h:mm:ss"
  vFormats(9, 1) = "D9": vFormats(9, 2) = "h:mm"
  vFormats(10, 1) = "G": vFormats(10, 2) = "General"
  
  For lCntr = 1 To UBound(vFormats)
  
     If vFormats(lCntr, 1) = zFMT Then
       
       If zFMT = "G" Then
         FormatDateToText = vDate
       Else
        FormatDateToText = Format(vDate, vFormats(lCntr, 2))
       End If
       
       Exit For
       
     Else
       FormatDateToText = ""
     End If
     
  Next lCntr
  
End Function 'FormatDateToText

Test Results:
46828-DateTextFmts

You’ll see I added a column for Spyware Doctors (SWD Method) solution, which works fine for text.

The UDF will handle both text and the limited number of date formats supported by the Cell function but as you can see in row 5 that has problems also.

Hopefully someone has a comprehensive solution to your problem.

Test File: 46829-Excel-VBA-Concatenate-Text-to-Date-preserving-Date-format

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

We Have a WINNER! :thewave:

Nice job! I’ve cleaned it up a little, very little!

Code:

Public Function ZeddyFMT(rngDate As Range) As String
     ZeddyFMT = rngDate.Text
End Function

Results:
46833-ZeddyFmt

Clearly handles all solutions! I’ll tuck this one into my bag of tricks for future use. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi RG

Many thanks! I love those dancing guys!

Now, it’s not just dates – you can use it for other stuff too.
For example, you might have a cell with a custom number format, e.g.
“CDN $”0.00
..so the cell might contain 25 but will actually display as CDN $25.00
..and the asDisplayed() function will fetch the displayed value “CDN $25.00” rather than 25

zeddy

Reply | Quote

What a useful device!!!

Is there any way to add to the code, so that the module will recalculate if the target cell is changed? I played around with it, and it currently could get me into trouble if I had multiple references present, and failed to note that some cells had not been updated.

Reply | Quote

John,

Just add the Application.Volatile statement and you should be good to go.

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote