I have a scenario I’m testing that creates a new list in Clickup when an event is created in Outlook 365. The basic parts of the scenario work fine and the test lists have been created. The next thing I’m trying to do, is to combine a shortened version of the start date with the event name, to create the list name. I’ve been able to do this using FormatDate, however the shortened date is a day early.
These events are all-day events. I’ll attach a screenshot that shows two test events, both set to start on 8/23. The first test does not have the formatting, so you can see the full date string and that it shows 8/23 in the string. The second test shows that it did format the date, but reads it as 8/22. I’m also attaching a screenshot that shows the formatting as MM/DD, as I now realize people may suggest that the 22 is for the current year. I did other tests using different dates and the day was always one day early.
I also tested the scenario with partial day events, meaning it’s not an all-day event; it has a start time and end time. When I ran those, the shortened date would be correct. I even tried a partial day event with a start time of 12am, because I know all-day events technically start at 12am, and maybe it was a timezone issue. But the partial day event starting at 12am, still formatted to 8/23.
At this point, the meat of what I need works, and I can just have the event coordinator type the short date ahead of the event name manually… It’s just frustrating and I want to figure it out.
From the organization dashboard click “change details” at the top right.
At the bottom left, click your name > Profile and change to the tab “TIME ZONE OPTIONS”.
The application/service you’re using also has a specific time zone. Often it is UTC. To fix your issue, you might need to adjust the time according to your time zone.
But the first and easiest option would be, to add a timezone to your formatDate function.
Thank you for the response Gijs! I’ve reviewed the two links and video. Unfortunately, I’ve not been able to figure out a proper* fix. The time zones for Make, Outlook, and Clickup are all set to EST (America/New York).
It’s peculiar because it seems like Outlook isn’t outputting the info correctly. I’ll attach a screenshot that shows it. The outlook calendar itself shows a 8/31 12:00am EST start time, but you can see that Make is receiving 8/30 8:00pm, but marked as UTC. Then when the scenario gets pushed into Clickup and creates the list name, the time goes back to 00:00:00, but still marked UTC, as seen in test 12. Then, when formatDate is applied it thinks it’s correcting the timezone and knocks 4 hours off the time, without even adding America/New_York (test15). If I do add the time zone to the formatDate formula, it doesn’t change anything (test 16), presumably because it thinks it’s already using that timezone. Side note, this issue only happens with all-day events. Regular events go through the steps and formatting as desired; even if I make them start at 12:00am like all-day events do (test 17).
OR… I’m completely misreading everything and screwing it all up. I honestly don’t know.
*But regardless, I did figure out that if I use addHours to add 5 hours to date and then format it, I get the result I want, I guess.
It looks like, Outlook uses UTC for their API. UTC is most often used for any kind of webservices. EST - UTC is currently 4 hours (Daylight time). When the clocks are set to winter time, it is again 5 hours. So if you add 5 hours, you should be okay for the whole year.
Depending on what services you use, it might be an option to only use UTC in all scenarios and databases and only convert to local time when you display the data to a customer, for example in an email or invoice.
But I can completely understand, if this feels a bit tricky since time zones can easily mess up your data.
I just want to thank you for the tip to add the extra hour for the whole year. I’m having the same issue as the OP, except mine is going into Monday. This time zone issue of not adjusting from Make/Outlook to the final destination is a real drag.
No problem. While I did get the concept working, we haven’t put the automation in place yet, so I can’t vouch for the solution in real-world usage. We have a couple other automations we’re using first, and will likely implement this Outlook/Make/ClickUp automation in the near future.
