Nate Lawrence
New Member
- Joined
- Sep 24, 2019
- Messages
- 6
- Office Version
- 365
- Platform
- Windows
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:
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:
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
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:
Code:
[h]"h"mm"m"ss"s"
---
I'm able to concatenate most fields in the following manner:
Code:
=HYPERLINK(CONCATENATE("https://youtube.com/watch?v=",$B$1,"&t=",B6),A6)
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