Dynamic YouTube Timecode URLs in Excel

[Nate Lawrence]

Hello, all.

I enjoy indexing chapters for long YouTube videos, so that others can jump to the topics which interest them.
For example, someone has created a series of videos with a brief overview of every Family Computer/N.E.S. game released in order of their original publish date.

The 2 following links demonstrate:
a) a hyperlink to Episode 5 of "Chrontendo"
b) a hyperlink to the beginning of the Super Mario Bros. coverage within Episode 5

https://youtube.com/watch?v=7YXoL7YZ9Ak
https://youtube.com/watch?v=7YXoL7YZ9Ak&t=6m52s

As you can see, timecodes for YouTube are added by adding "&t=XhYmZs" to the end of the link to a video
(where X is the number of hours, Y is the number of minutes, and Z is the number of seconds that you wish to seek to).

---

In Excel I can use the HYPERLINK function to create dynamic hyperlinks but I'm having a difficult time getting the timecodes formatted in the above manner within the cell which uses =HYPERLINK function.

---

I have:
1) a column A where I input chapter titles,
2) a column B where I input timecodes in a 0:06:52 style,
3) a cell B1 where I store the video ID for a given video (In the above case it would be 7YXoL7YZ9Ak .)
4) a column where I would like to dynamically concatenate the date into a clickable URL.

---

I know that if I format column B as:

[h]"h"mm"m"ss"s"

then it looks how it needs to be appended to the end of a YouTube URL, ( 0h06m52s ) however cell formatting doesn't affect how the formula accesses the value.

---

I'm able to concatenate most fields in the following manner:

=HYPERLINK(CONCATENATE("https://youtube.com/watch?v=",$B$1,"&t=",B6),A6)

where B1 contains "7YXoL7YZ9Ak", B6 contains "0:06:52", A6 contains "Super Mario Bros.".

Where I'm failing is that the formula reads "0:06:52" as "0.00476851851851852" which results in:
https://youtube.com/watch?v=7YXoL7YZ9Ak&t=0.00476851851851852 (non-functional)
I need to know how to control the formatting of cell B6 within the formula.

---

Your assistance is greatly appreciated.
.nl

 

[jasonb75]

Try

=HYPERLINK("https://youtube.com/watch?v="&$B$1&"&t="&TEXT(B6,"[h]\hmm\mss\s"),A6)

 

[Nate Lawrence]

That works, Boss!

Many thanks.
I'd tried using the TEXT function but couldn't get the syntax correct.

Your formula works like a charm.