Adding time format in the sentence

[aryanaveen]

Hi Guys

please help me with below code

Basically I am trying to combine values from multiple cells to one sentence.
Range B16 has date, Range B17 is time in "hh:mm" format

when I use below code, time is getting converted in to numbers

Range("B7").Value = "I come on " & Range("B16").Value & " " & Range("B17").Value & " with " & Range("D1").Value

Result what I am getting is "I come on Friday, July 31, 2020 0.6375 with Naveen

Expected result - "I come on Friday, July 31, 2020 02:30 PM with Naveen

 

[Peter_SSs]

Try

Range("B7").Value = "I come on " & Range("B16").Value & " " & Format(Range("B17").Value, "hh:mm AM/PM") & " with " & Range("D1").Value

 

[aryanaveen]

Thank you Peter, this is working as expected.

is there a way to make only date and time bold

 

[Peter_SSs]

Thank you Peter, this is working as expected.

You're welcome.

is there a way to make only date and time bold

Yes, I'll come back with that

 

[Peter_SSs]

Yes, I'll come back with that

This would do it ...

Sub Test1()
  With Range("B7")
    .Value = "I come on " & Range("B16").Value & " " & Format(Range("B17").Value, "hh:mm AM/PM") & " with " & Range("D1").Value
    .Characters(11, InStr(1, .Value, ":") - 5).Font.Bold = True
  End With
End Sub

.. but this more general code might be more useful if you have to adapt.

Sub Test2()
  Dim sPrefix As String, sSuffix As String
  
  sPrefix = "I come on "
  sSuffix = " with " & Range("D1").Value
  
  With Range("B7")
    .Value = sPrefix & Range("B16").Value & " " & Format(Range("B17").Value, "hh:mm AM/PM") & sSuffix
    .Characters(Len(sPrefix) + 1, InStr(1, .Value, sSuffix) - Len(sPrefix) - 1).Font.Bold = True
  End With
End Sub