Format a Date with Words in a Hyperlink

OilEconomist

Active Member
Joined
Dec 26, 2016
Messages
374
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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,915
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

Active Member
Joined
Dec 26, 2016
Messages
374
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

Active Member
Joined
Dec 26, 2016
Messages
374
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,915
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

Active Member
Joined
Dec 26, 2016
Messages
374
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,637
Messages
5,838,497
Members
430,551
Latest member
digitalbrolly

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