OilEconomist
Active Member
- Joined
- Dec 26, 2016
- Messages
- 439
- Office Version
- 2019
- Platform
- Windows
Thanks in advance and will try to give feedback on whether it works or not.
I am trying to format a date in a hyperlink. I want the date and add phrases with it. Such as "2020-07-22, Morning, Yes" I attempted to use concatenate, but it would not work. Here is the simplified code.
I am trying to format a date in a hyperlink. I want the date and add phrases with it. Such as "2020-07-22, Morning, Yes" I attempted to use concatenate, but it would not work. Here is the simplified code.
Code:
'*****************************************************************************************************
Sub DateF()
'____________________________________________________________________________________________
'Turn off alerts, screen updates, and automatic calculation
'Turn off Display Alerts
Application.DisplayAlerts = False
'Turn off Screen Update
Application.ScreenUpdating = False
'Calculate to ensure all values are updated
Calculate
'Turn off Automatic Calculations
Application.Calculation = xlManual
'____________________________________________________________________________________________
'Dimenisoning/Declaring Variables
Dim Date1 As Date
Dim When1 As String
Dim Confirm1 As String
'____________________________________________________________________________________________
'Main Code in this section
Sheets(Sheet1).Activate
Date1 = Range("A1").Value
When1 = Range("B1").Value
Confirm1 = Range("C1").Value
'Cell D1 has a hyperlink to be renamed
Range("D1").Select
Selection.Hyperlinks(1).TextToDisplay = _
Date1 & ", " & When1 & ", " & Confirm1
'Would like the format to be like this: "2020-07-14, Morning, Yes"
'not including the quotes though
'I tried concatenate
'=CONCATENATE(Text(EarningsDate, "YYYY-MM-DD DDD"), ", ", When, ", ", Confirmation)
'_______________________________________________________________________________________________
'Turn on alerts, screen updates, and calculate
'Turn On Display Alerts
Application.DisplayAlerts = True
'Turn on Screen Update
Application.ScreenUpdating = True
'Calculate to ensure all values are updated
Calculate
'Turn off Automatic Calculations
Application.Calculation = xlManual
End Sub