+ Reply to Thread
Results 1 to 27 of 27

Help with Hyperlink to today's date

  1. #1
    Registered User
    Join Date
    03-20-2024
    Location
    oslo
    MS-Off Ver
    10
    Posts
    9

    Help with Hyperlink to today's date

    Hi everyone.

    I have a small problem with a hyperlink in my form. which calculates wages based on hours worked, before and after taxes.
    I have a hyperlink at the top of the page that could take you directly to today's date in the form, but it doesn't work. And i can't for the life of me understand why. I've scoured the internet and tried tons of ways to get this to work, but I always end up with a broken link or errors.

    What I'm trying to accomplish is that the hyperlink takes you to today's date and highlights the date cell in a different color.

    Does anyone here have any good ideas on how I should proceed?.
    Attached Files Attached Files

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.90 for Mac MS 365
    Posts
    8,583

    Re: Help with Hyperlink to today's date

    I did it using a helper cell where I put the address that shows for today. then pointed the hyperlink at it. see attached for I2 that has the address for the cell for today and the hyper link that was changed in H2.
    Attached Files Attached Files
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    03-20-2024
    Location
    oslo
    MS-Off Ver
    10
    Posts
    9

    Re: Help with Hyperlink to today's date

    Hi sam. Thanks for your quick answer.
    I'm sorry, but it doesn't work on my computer. I have put the form on my google drive, and granted you editing rights. Maybe you can make it work there?.

    docs.google.com/spreadsheets/d/1csHVIhcWQBaXwI6e_9fIKHmH7BpOi0kQ/edit#gid=1326507239

    .

  4. #4
    Forum Expert
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 V2401 w/ Win10 Home 64 Bit
    Posts
    2,014

    Re: Help with Hyperlink to today's date

    It worked fine with me, could it be a regional version problem?

  5. #5
    Registered User
    Join Date
    03-20-2024
    Location
    oslo
    MS-Off Ver
    10
    Posts
    9

    Re: Help with Hyperlink to today's date

    No, I do not think so. I tested the hyperlink both on my computer and on online excel editors with the same result.
    But in the link I gave you above, you can edit the form online directly, and if it works there, it works globally regardless of versions.

  6. #6
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.90 for Mac MS 365
    Posts
    8,583

    Re: Help with Hyperlink to today's date

    Ok, the issue - at least in the google sheets version, is that you have to replace the commas with semicolons, so my version uses commas so this formula in I2 =ADDRESS(MATCH(TODAY(),C:C,0),3,4,1)
    will need to be (in that version of google sheets) =ADDRESS(MATCH(TODAY();C:C;0);3;4;1)
    also hypperlink will have to change to =HYPERLINK("#"&I2;"Gå till dagens datum")
    give those changes a try to see if that works.

  7. #7
    Forum Expert
    Join Date
    10-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365 V2401 w/ Win10 Home 64 Bit
    Posts
    2,014

    Re: Help with Hyperlink to today's date

    That sort of thing would justify why it worked with me so fine.

  8. #8
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.90 for Mac MS 365
    Posts
    8,583

    Re: Help with Hyperlink to today's date

    That sort of thing would justify why it worked with me so fine.
    Thanks for the support!
    guess we'll have to wait to see what hohamer9876 says.

  9. #9
    Registered User
    Join Date
    03-20-2024
    Location
    oslo
    MS-Off Ver
    10
    Posts
    9

    Re: Help with Hyperlink to today's date

    No, sorry, it doesn't work. Can you change it directly in the sheet please.
    Is there something easier to achieve this?. Like the 'today's date' is marked directly when you open that page documen?.

  10. #10
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.90 for Mac MS 365
    Posts
    8,583

    Re: Help with Hyperlink to today's date

    well I hadn't noticed the merged cell before in G2:H2 so the formula for the hyperlink should be in G2 instead of H2. But apart from that the only portion I'm able to get to work is the address in the sheet but not the hyperlink.

  11. #11
    Registered User
    Join Date
    03-20-2024
    Location
    oslo
    MS-Off Ver
    10
    Posts
    9

    Re: Help with Hyperlink to today's date

    I tried this: =HYPERLINK("#C"&MATCH(TODAY(),C8:C38;0)+7," Today's date")
    But that didn't work either.

    Is it possible to mark the 'today's date' directly when the document loads?.

  12. #12
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.90 for Mac MS 365
    Posts
    8,583

    Re: Help with Hyperlink to today's date

    did you try this adjustment to your hyperlink formula... =HYPERLINK("#C"&MATCH(TODAY();C8:C38;0)+7;" Today's date")

    as far as
    Is it possible to mark the 'today's date' directly when the document loads?.
    this formula will always show today's date when opening the document... =TODAY()

  13. #13
    Registered User
    Join Date
    03-20-2024
    Location
    oslo
    MS-Off Ver
    10
    Posts
    9

    Re: Help with Hyperlink to today's date

    =HYPERLINK("#C"&MATCH(TODAY();C8:C38;0)+7;" Today's date") didn't work either, or am i doing it wrong?.
    Please test your theories here. Edit away as much as you want


    docs.google.com/spreadsheets/d/1csHVIhcWQBaXwI6e_9fIKHmH7BpOi0kQ/edit#gid=1326507239

    .

  14. #14
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.90 for Mac MS 365
    Posts
    8,583

    Re: Help with Hyperlink to today's date

    I am having no luck with changes to your google sheet, but I have no experience (except for today) working in google sheets. I can only get the address to work, the hyperlink doesn't error out, it just doesn't activate.

  15. #15
    Registered User
    Join Date
    03-20-2024
    Location
    oslo
    MS-Off Ver
    10
    Posts
    9

    Re: Help with Hyperlink to today's date

    It is exactly the same result that I get in Calc in Openoffice. Wondering why this is happening.
    Is there any code that can be used, that will highlight that correct date in the date column #C directly when the document is loaded instead?.

  16. #16
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.90 for Mac MS 365
    Posts
    8,583

    Re: Help with Hyperlink to today's date

    I've bumped this issue to people with more experience than I have to see if they can offer a better or different WORKING solution. Good luck.

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2411 (Windows 11 23H2 64-bit)
    Posts
    87,961

    Re: Help with Hyperlink to today's date

    Is this a GoogleSheets or an Excel question?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    NB:
    as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.

  18. #18
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    46,680

    Re: Help with Hyperlink to today's date

    @Sam: I checked out your approach. The Helper cell works for me:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Where I2 contains:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    The formula evaluated to C28

    So then I tried replacing the Helper cell with the formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    This also worked for me so, in theory, you don't need a Helper cell.
    Attached Files Attached Files
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  19. #19
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.90 for Mac MS 365
    Posts
    8,583

    Re: Help with Hyperlink to today's date

    @Ali, it appears to me it is a google sheets issue as every time i go to the link it opens google sheets. I really have never worked in google sheets so I am not sure my formula should work.

    @TMS, I was sure it could be done in one formula w/o a helper cell, thanks for working it out.
    will have to see what hohamer has to say.

  20. #20
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2411 (Windows 11 23H2 64-bit)
    Posts
    87,961

    Re: Help with Hyperlink to today's date

    I've moved it to the GoogleSheets section.

  21. #21
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    46,680

    Re: Help with Hyperlink to today's date

    Maybe this: https://docs.google.com/spreadsheets...),C:C,0),3,4,1)

    But, when I tried to edit a Title, I messed it up

    See: https://support.google.com/docs/answ...-a-spreadsheet

  22. #22
    Registered User
    Join Date
    03-20-2024
    Location
    oslo
    MS-Off Ver
    10
    Posts
    9

    Re: Help with Hyperlink to today's date

    No worries my friend
    You messing things up means that you are testing and experimenting. Keep on trucking (:

    I have backups... so I can just restore it. if we lose everything.

  23. #23
    Registered User
    Join Date
    03-20-2024
    Location
    oslo
    MS-Off Ver
    10
    Posts
    9

    Re: Help with Hyperlink to today's date

    Excel question.

    However, I don't have an Excel editor on a server. so google are serving as a mediator for those who want to try to LIVE testing and editing their theories, on how we solve this mystery.

    .

  24. #24
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    46,680

    Re: Help with Hyperlink to today's date

    I shouldn’t mess anything up. I put the link I was testing in the cell above yours so, if it doesn't work, we can just delete it.

    I followed the instructions to create and copy a link to a specific cell, C28 in this case. That worked and then I replaced C28 with the formula, and that worked. It's only when I tried to put a link name in that it went wrong.

    Problem is, I don't really know how to drive Google Sheets.

  25. #25
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,131

    Re: Help with Hyperlink to today's date

    You need to include the gid of the sheet.

    Here is the final working formula for the new sheet named "Kopia av Tidrapport"

    Please Login or Register  to view this content.
    Note that you must change the gid to suit the sheet that you are trying to reference.

    You can get the gid from the URL in your browser.
    As a gesture off appreciation, you can click * Add Reputation at the foot of any of the posts of members who helped you reach a solution.

    And finally, was your problem solved? if so, please click Thread Tools above the first post of your enquiry, then select [Solved]

  26. #26
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2411 (Windows 11 23H2 64-bit)
    Posts
    87,961

    Re: Help with Hyperlink to today's date

    Quote Originally Posted by hohamer9876 View Post
    Excel question.

    However, I don't have an Excel editor on a server. so google are serving as a mediator for those who want to try to LIVE testing and editing their theories, on how we solve this mystery.

    .
    Why Google? Members can open workbooks in the 365 browser if you instruct them to do so. I think using Google is inevitably going to lead to errors when ported to Excel.

    I have moved the question back to the Excel section.

  27. #27
    Valued Forum Contributor janmorris's Avatar
    Join Date
    07-24-2021
    Location
    Japan
    MS-Off Ver
    Google Sheets (& Mac 2021)
    Posts
    1,131

    Re: Help with Hyperlink to today's date

    I have provided solution for XLSX stored in Google Drive.

    If the user has the file on their own computer then they would need to create a HYPERLINK formula based on MS Office standards.

    Office support link: HYPERLINK function - Microsoft Support
    https://support.microsoft.com/en-au/...7-7de9b76f577f

    Screenshot 2024-03-22 at 17.14.58.png


    I have tested these working in Office 16 for Mac:

    Please Login or Register  to view this content.
    Last edited by janmorris; 03-22-2024 at 09:30 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Hyperlink to TODAY date in a range
    By BassemCh in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 12-02-2021, 01:33 PM
  2. Hyperlink to Today's Date
    By Maid1812 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-29-2020, 08:57 AM
  3. [SOLVED] Jump to Today's Date with hyperlink or Macro
    By Crawfy in forum Excel General
    Replies: 8
    Last Post: 04-05-2017, 05:04 PM
  4. Enter today date on other sheet from hyperlink
    By unley in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-09-2017, 03:36 AM
  5. [SOLVED] Hyperlink to today's date
    By Trebor777 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-17-2016, 10:41 AM
  6. [SOLVED] Hyperlink to today's date
    By Trebor777 in forum Excel General
    Replies: 10
    Last Post: 09-07-2016, 11:59 AM
  7. [SOLVED] Hyperlink to jump to today's date
    By marcimarc in forum Excel General
    Replies: 3
    Last Post: 12-19-2013, 10:31 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1