Format a Date with Words in a Hyperlink

OilEconomist

Board Regular
Joined
Dec 26, 2016
Messages
226
Office Version
  1. 2019
Platform
  1. 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.

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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,427
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Maybe:
VBA Code:
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

Board Regular
Joined
Dec 26, 2016
Messages
226
Office Version
  1. 2019
Platform
  1. Windows
Thanks @JoeMo

I tested the code and it gave the error:

"Run-time error '9':

Subscript out of range"
 

OilEconomist

Board Regular
Joined
Dec 26, 2016
Messages
226
Office Version
  1. 2019
Platform
  1. Windows
@JoeMo Hold up just a second. I think I might have something else wrong with my code.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,427
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Board Regular
Joined
Dec 26, 2016
Messages
226
Office Version
  1. 2019
Platform
  1. Windows
@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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,752
Messages
5,638,162
Members
417,011
Latest member
Amaden95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top