Format a Date with Words in a Hyperlink

[OilEconomist]

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.

'*****************************************************************************************************
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

 

[JoeMo]

Maybe:

Sub Example()
'Assumes cell D1 on active sheet contains a hyperlink to be renamed
Dim Date1 As Date
Dim When1 As String
Dim Confirm1 As String
Date1 = Range("A1").Value
When1 = Range("B1").Value
Confirm1 = Range("C1").Value
Range("D1").Hyperlinks(1).TextToDisplay = Format(Date1, "yyyy-mm-dd") & ", " & When1 & ", " & Confirm1
End Sub

 

[OilEconomist]

Thanks @JoeMo

I tested the code and it gave the error:

"Run-time error '9':

Subscript out of range"

 

[OilEconomist]

@JoeMo Hold up just a second. I think I might have something else wrong with my code.

 

[JoeMo]

Thanks @JoeMo

I tested the code and it gave the error:

"Run-time error '9':

Subscript out of range"

Did you read the comment in the code? Do you have a hyperlink in cell D1 of the activesheet before you run the code? If not, you will get that error? Your OP says 'Cell D1 has a hyperlink to be renamed - is that the case?

 

[OilEconomist]

@JoeMo As I indicated, I thought there was an issue with my code.

I was running a loop and there was a blank row and that was the issue. I put an If then to fix that.

Thanks so much as your solution worked perfectly.