Did you know you can calculate the timezone offset directly in a flow using just formulas and a text template? No Apex!
These formulas take advantage of text templates getting the date/time in the user's timezone while the function TEXT( {!now} ) gets the same date/time in UTC.
1️⃣ create a formula (it can be any date/time instead of NOW())
👉 now = NOW()
2️⃣ create a text template (use plain text)
👉 currentDateTimeTemplate = {!now}
3️⃣ create these other formulas
👉 UTCDateTimeText = TEXT( {!now} )
👉 hourInUserTZ = VALUE( MID( {!currentDateTimeTemplate}, FIND( " ", {!currentDateTimeTemplate} ) + 1, FIND( ":", {!currentDateTimeTemplate} ) - FIND( " ", {!currentDateTimeTemplate} ) - 1 ) )
👉 UTChour = VALUE( MID( {!UTCDateTimeText}, FIND( " ", {!UTCDateTimeText} ) + 1, FIND( ":", {!UTCDateTimeText} ) - FIND( " ", {!UTCDateTimeText} ) - 1 ) )
👉 localTZdate = DATEVALUE( MID( {!currentDateTimeTemplate}, 1, 10 ) )
👉 UTCdate = DATEVALUE( MID( {!UTCDateTimeText}, 1, 10 ) )
👉 timezoneDifference = {!hourInUserTZ} - {!UTChour}
- IF( {!UTCdate} > {!localTZdate}, 24, IF( {!UTCdate} < {!localTZdate}, -24, 0 ) )
+ IF( CONTAINS( {!currentDateTimeTemplate}, "PM" ) && {!hourInUserTZ} < 12, 12, 0 )
- IF( CONTAINS( {!currentDateTimeTemplate}, "AM" ) && {!hourInUserTZ} = 12, 12, 0 )
These formulas do an adjustment for when the user's and UTC times fall into different days and converts the user's local hours to military hours to complete the calculation of the timezone offset.
If the user has an uncommon locale, you may have to adjust the formulas that extract hourInUserTZ and localTZdate.
🔗 LINK to the flow metadata that you can import via VSCode: https://lnkd.in/gdNTiE58
~~~~~~~~~~~~~~~~~~
🙏 A Note of Gratitude 🙏
This elegant solution was originally suggested in 2019 by the late Marc B.
His innovative contributions continue to inspire us.
Thank you and rest in peace, Marc! Even though many of us didn't have the privilege of meeting you, your brilliance lives on.
🔗 LINK to the original post: https://lnkd.in/guyUVEWi
~~~~~~~~~~~~~~~~~~
🙏 Thanks to Yossi Altein for calling attention to my mistake: I was comparing the UTC and local timezone days instead of dates and that wouldn't work at end of months. I've replaced the formulas to extract dates instead. Unfortunately the images I had posted can't be replaced now.
🔗 Here is Yossi's version:
https://www.linkedin.com/posts/yossialtein_saleforce-salesforceflow-salesforcetools-activity-7285457788330201089-KJNa?utm_source=share&utm_medium=member_desktop
