Insert Google Calendar date from cell?

Question

I have a link in my Google Sheet to create a Google Calendar entry for the item:

=HYPERLINK("https://calendar.google.com/calendar/render?action=TEMPLATE&text="&A7&"&dates="&B7&"&details=&location=","Add to Calendar")

The date format in B7 the cell is DD-MMM-YYYY for example 13-Jan-2022

I have seen the format for the link needs to be:

YYYYMMDDTHHmmSSZ/YYYYMMDDTHHmmSSZ

How can I convert the date for it to work as an all-day item, not a set time?

Thank you!

Hi! Try this one: https://stackoverflow.com/a/37659516/11832197 — onit, Mar 10 '22 at 12:17

Mike Steelson · Accepted Answer · 2022-03-10T12:57:00.550

1

You have to convert the dates in GMT Greenwich Mean Time

format: YYYYMMDDTHHmmSSZ/YYYYMMDDTHHmmSSZ

EXAMPLE 20220113120000Z/20220113130000Z

Try

=HYPERLINK("https://calendar.google.com/calendar/render?action=TEMPLATE&text="&A7&"&dates="&text(B7,"YYYYMMDDTHHmmSS")&"Z/"&text(B7,"YYYYMMDDTHHmmSS")&"Z&details=&location=","Add to Calendar")

edit

with GMT correction

=HYPERLINK("https://calendar.google.com/calendar/render?action=TEMPLATE&text="&A2&"&dates="&text(B2-value(substitute(substitute(D2,"+",""),"GMT",""))/24,"YYYYMMDDTHHmmSS")&"Z/"&text(C2-value(substitute(substitute(D2,"+",""),"GMT",""))/24,"YYYYMMDDTHHmmSS")&"Z&details=&location=","Add to Calendar")

edited Mar 10 '22 at 12:57

answered Mar 10 '22 at 12:31

Mike Steelson

14,650
2
5
20

Yep I saw the format, my question was how :) – user3332692 Mar 10 '22 at 12:37
ok, I have given you a formula – Mike Steelson Mar 10 '22 at 12:40
What is your locale? I will then add hours to translate in GMT. – Mike Steelson Mar 10 '22 at 12:40
Thank you, I am based in the UK – user3332692 Mar 10 '22 at 12:45

Insert Google Calendar date from cell?

1 Answers1

edit