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!

Answer 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")