Help with Formatting 24-Hour Time from Google Forms for Google Calendar

[NITE]

I'm setting up a workflow where I need to create Google Calendar events from a Google Form submission. I've got everything mapped out in Pabbly Connect, but I’m running into an issue with formatting the time correctly. The time from my form is in 24-hour format (HH:mm:ss), and I need to convert this to the ISO 8601 format required by Google Calendar (YYYY-MM-DDTHH:mm:ssZ).

The specific problem is that the year is showing as "20248" when it should be "2024", and the time is shifting from "8:00:00" to "02:00:00", which is incorrect. I’ve checked my Date/Time Formatter settings, and it looks like I'm not selecting the correct 'From Format' since all options include AM/PM, and there isn't an option without AM/PM for a 24-hour format.

Has anyone faced this issue before or could provide guidance on the correct way to format the time for Google Calendar using Pabbly Connect? Below are the details of my current setup for clarity:

• Date from Google Form: 2/04/2024 8:00:00
• Expected format for Google Calendar: YYYY-MM-DDTHH:mm:ssZ
• Timezone: Europe/Madrid

Your insights or suggestions would be greatly appreciated!

 

[Pabblymember11]

Hey @NITE

Can you please share the workflow URL once in which you have experienced the same?

 

[NITE]

Pabbly - Online Marketing & Sales Software

Automate your sales and marketing with Pabbly. Unite email marketing, lead capture, WordPress themes, subscription all at one place.

connect.pabbly.com

 

[Pabblymember11]

You have to change the time format and the Timezone from your "Google Calendar" action event since the data you are providing is already in UTC , so you do not need to change the timezone from your Google Calendar action event. Kindly change it to UTC to balance the Timezone when the event gets created.

However, with the help of Date Time Formatter, you can change the time zone to your desired time zone accordingly. Kindly review your workflow once we have made certain changes to it accordingly.

You can add a Date/Time Formatter to your workflow, subtract 2 hours from the time, and then map that time in Google Calendar.

 

[NITE]

I appreciate the support provided thus far. However, I am still facing challenges with the Date/Time Formatter configuration. If it is possible, may I request the assistance of the Pabbly support team to directly make the necessary adjustments to my workflow configuration? Here are the issues that need addressing:

1. The year is incorrectly being concatenated as 20248 when merging the event date and start time, resulting in an incorrect date format.
2. I am unable to find a 'From Format' option in the Date/Time Formatter that accommodates a 24-hour clock without AM/PM, which my Google Form data requires.

If the support team is willing to make these adjustments on my behalf, I would be incredibly grateful. Please feel free to make the pertinent changes as deemed appropriate to correct the formatting issue, ensuring that the dates and times align correctly for Google Calendar event creation.

Should you require any additional information or access to perform these adjustments, please let me know, and I will provide it promptly.

Thank you very much for your time and assistance.

 

[Pabblymember11]

Hey @NITE

Please check your workflow once and then try again. I hope this will work for you.

 

[NITE]

Thank you, I replicated the procedure for the end time.
however, when setting the google calendar block it returns bad request. Where is the errror?

 

[NITE]

I managed to fix it. However, it turns the day the month and vice versa... here's a video of the whole process: https://sendspark.com/share/z7lolyub4s1h7gfig3b9spfofmi94jsm

 

[NITE]

I don't know if you have modified anything, now the date is correct, but it books the time with extra +2 hours.
I booked from 9pm - 10pm and it shows at 11pm - 00 am in Google Calendar

 

[ArshilAhmad]

To fix this issue, you can add a Date/Time Formatter to your workflow to subtract 2 hours from the original time and then map the resulting time in Google Calendar. This will help you create events with the correct time in Google Calendar.

 

[NITE]

Thank you. Finally, I managed to make it work!