Conversion of timezones (Solved Formula Included)

L_123
L_123 ✭✭✭✭✭✭
edited 12/09/19 in Smartsheet Basics

This is just to share a rather difficult solution I was required to develop this morning, converting the California server time to Central Time in a created column (Named "Created"). If anyone has a better solution I would love to see it.

First thing I did was calculate how many minutes have passed in the year referenced in the created column with the formula below in a column named "Year -> Minutes" I also added in the 2 hour discrepancy in the time zones (bold and underline in the formula below).

=(NETDAYS(DATE(YEAR(Created@row), 1, 1), Created@row) * 1440 + (60 * (2 + IF(AND(RIGHT(Created@row, 2) = "PM", NOT(IFERROR(VALUE(MID(Created@row, 10, 2)), 1) = 12)), 12) + VALUE(MID(Created@row, 10, LEN(Created@row) - 15)))) + VALUE(LEFT(RIGHT(Created@row, 5), 2)))

Next I referenced this to keep a smaller working formula, and rebuilt the year in the proper format

LEFT(DATE(YEAR(Created1), 1, 1) + ROUNDDOWN([Year -> Minutes]@row/ 1440) - 1, 8) + " " + ROUNDDOWN(MOD([Year -> Minutes]@row, 1440) / 60) + ":" + 60 * MOD(MOD([Year -> Minutes]@row, 1440) / 60, 1)

Finally I replaced all instances of [Year->Minutes]@row with the first formula.

=LEFT(DATE(YEAR(Created1), 1, 1) + ROUNDDOWN((NETDAYS(DATE(YEAR(Created@row), 1, 1), Created@row) * 1440 + (60 * (2 + IF(AND(RIGHT(Created@row, 2) = "PM", NOT(IFERROR(VALUE(MID(Created@row, 10, 2)), 1) = 12)), 12) + VALUE(MID(Created@row, 10, LEN(Created@row) - 15)))) + VALUE(LEFT(RIGHT(Created@row, 5), 2))) / 1440) - 1, 8) + " " + ROUNDDOWN(MOD((NETDAYS(DATE(YEAR(Created@row), 1, 1), Created@row) * 1440 + (60 * (2 + IF(AND(RIGHT(Created@row, 2) = "PM", NOT(IFERROR(VALUE(MID(Created@row, 10, 2)), 1) = 12)), 12) + VALUE(MID(Created@row, 10, LEN(Created@row) - 15)))) + VALUE(LEFT(RIGHT(Created@row, 5), 2))), 1440) / 60) + ":" + IF(LEN(60 * MOD(MOD((NETDAYS(DATE(YEAR(Created@row), 1, 1), Created@row) * 1440 + (60 * (2 + IF(AND(RIGHT(Created@row, 2) = "PM", NOT(IFERROR(VALUE(MID(Created@row, 10, 2)), 1) = 12)), 12) + VALUE(MID(Created@row, 10, LEN(Created@row) - 15)))) + VALUE(LEFT(RIGHT(Created@row, 5), 2))), 1440) / 60, 1)) = 1, "0", "") + 60 * MOD(MOD((NETDAYS(DATE(YEAR(Created@row), 1, 1), Created@row) * 1440 + (60 * (2 + IF(AND(RIGHT(Created@row, 2) = "PM", NOT(IFERROR(VALUE(MID(Created@row, 10, 2)), 1) = 12)), 12) + VALUE(MID(Created@row, 10, LEN(Created@row) - 15)))) + VALUE(LEFT(RIGHT(Created@row, 5), 2))), 1440) / 60, 1)

 

Smartsheet employees, can yall make this easier? I don't really feel like I should have had to go through all his to add 2 hours to a date/time. Also an option to change the time format would be nice. having the number of characters constantly changing made this more of a pain than it should have been.

 

 

*************************************************************************************************

Updated formula below. A few glitches in the top one, and made it shorter and faster.

=DATE(YEAR(Created@row), 1, 1) + ROUNDDOWN(((NETDAYS(DATE(YEAR(Created@row), 1, 1), Created@row) - 1) * 1440 + 60 * (VALUE(MID(Created@row, 10, LEN(Created@row) - 15)) + IF(AND(RIGHT(Created@row, 2) = "PM", NOT(VALUE(MID(Created@row, 10, LEN(Created@row) - 15)) = 12)), 12, 0) + 2) + VALUE(LEFT(RIGHT(Created@row, 5), 2))) / 1440) + " " + ROUNDDOWN(MOD(((NETDAYS(DATE(YEAR(Created@row), 1, 1), Created@row) - 1) * 1440 + 60 * (VALUE(MID(Created@row, 10, LEN(Created@row) - 15)) + IF(AND(RIGHT(Created@row, 2) = "PM", NOT(VALUE(MID(Created@row, 10, LEN(Created@row) - 15)) = 12)), 12, 0) + 2) + VALUE(LEFT(RIGHT(Created@row, 5), 2))), 1440) / 60) + ":" + LEFT(RIGHT(Created@row, 5), 2)

· Share on FacebookShare on Twitter

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I haven't plugged this into a sheet yet to see if maybe it can be simplified any, but one thing I have found is that when working with the time portion and the changing number of characters, using the FIND function helped A LOT.

    .

    FIND(" ", Created@row) + 1

    Will give you the location of the first digit of the hour within the string.

    .

    FIND(":", Created@row)

    Will give you the location of the colon which we know comes after the hour.

    .

    FIND(":", Created@row) - (FIND(" ", Created@row) + 1)

    Subtracting the first result from the second result will give you a number of how many digits are between the space and the colon which will tell you how many digits your hour contains.

    .

    Building those into a MID statement instead of specifying numbers will help automate that portion of things.

    .

    MID(Created@row, FIND(" ", Created@row) + 1

    to establish the starting point or your MID statement.

    .

    MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - (FIND(" ", Created@row) + 1))

    to establish how many digits the MID statement should pull.

    .

    Additionally, the minutes will always be the two digits immediately after the colon.

    =VALUE(MID(Created@row, FIND(":", Created@row) + 1, 2))

    to retrieve the minutes.

    .

    I personally find it easier to build out time calculations based on a 24 hour clock instead of the 12 hour. That conversion is also (relatively) simple once you have pulled your hours from the time portion.

    .

    VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - (FIND(" ", Created@row) + 1)))

    to convert the hours to a numerical value

    .

    VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - (FIND(" ", Created@row) + 1))) + IF(FIND("P", Created@row) > 0, 12)

    will add 12 to the hours if a "P" is found (meaning "PM").

    .

    Adding 2 to that number will give you the new hours.

    =VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - (FIND(" ", Created@row) + 1))) + IF(FIND("P", Created@row) > 0, 12) + 2

    .

    I suggest leaving the above in their own two columns to make future calculations easier and formulas a little bit shorter/easier to handle. For this example, we can call them "Minutes" and "[Add Hours]" respectively.

    .

    .

    Next we determine if that pushes us into "tomorrow"

    =IF([Add Hours]@row>= 24, 

    .

    If it does, we want to add 1 to the date portion of the Created column.

    =IF([Add Hours]@row>= 24, 1) + DATEONLY(Created@row)

    To give us the date for two hours later.

    We can call this column "[New Date]" and leave it as is in a Date type column.

    .

    Next we need to rework the hours.

    If the hours plus two is greater than 24 pushing us into the next day, we want to take that number and subtract 24 from it.

    =[Add Hours]@row - IF([Add Hours]@row >= 24, 24)

    We can call this column "[New 24 Hour]"

    .

    Next we determine if the new hour is AM or PM and use this base in two separate columns. First column will be [New 12 Hour] for the hour number, and the second column will simply be [AM/PM] which will generate one or the other.

    .

    =IF([New 24 Hour]@row >= 12, 

    This is the base for the two columns.

    .

    IF([New 24 Hour]@row >= 12, -12) + [New 24 Hour]@row

    Will give us the new 12 hour portion

    .

    IF([New 24 Hour]@row >= 12, "PM", "AM")

    Will give us one or the other appropriately.

    .

    Next we can take our New Date, add in a space, add the new hours, then a colon, then the minutes get added back in, then our AM/PM to bring it all back together.

     

    =[New Date]@row + " " + [New 12 Hour]@row + ":" + Minutes@row + [AM/PM]@row

    .

    .

    .

    The above will require tweaking if you anticipate the possibility of a 12:##AM time, but this should cover everything else. Obviously you can combine formulas in place of cell reference to cut down on additional columns. It is just my person preference to break it out into smaller sections.

     

    Just another option to working with time until SS is able to roll out more user friendly features for this. From my understanding, it is "in progress", but we don't have any kind of eta just yet for when it will be released.

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Formula for single column solution using the above methods:

    .

    =(IF(VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - (FIND(" ", Created@row) + 1))) + IF(FIND("P", Created@row) > 0, 12) + 2 >= 24, 1) + DATEONLY(Created@row)) + " " + (IF((VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - (FIND(" ", Created@row) + 1))) + IF(FIND("P", Created@row) > 0, 12) + 2) - IF(VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - (FIND(" ", Created@row) + 1))) + IF(FIND("P", Created@row) > 0, 12) + 2 >= 24, 24) >= 12, -12) + ((VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - (FIND(" ", Created@row) + 1))) + IF(FIND("P", Created@row) > 0, 12) + 2) - IF(VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - (FIND(" ", Created@row) + 1))) + IF(FIND("P", Created@row) > 0, 12) + 2 >= 24, 24))) + ":" + VALUE(MID(Created@row, FIND(":", Created@row) + 1, 2)) + (IF((VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - (FIND(" ", Created@row) + 1))) + IF(FIND("P", Created@row) > 0, 12) + 2) - IF(VALUE(MID(Created@row, FIND(" ", Created@row) + 1, FIND(":", Created@row) - (FIND(" ", Created@row) + 1))) + IF(FIND("P", Created@row) > 0, 12) + 2 >= 24, 24) >= 12, "PM", "AM"))

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • Anthony Barthelemy
    Anthony Barthelemy ✭✭✭✭✭✭

    Can you share your sheet so I can make a copy and see it in action?

     

    Thanks

    · Share on FacebookShare on Twitter
  • L_123
    L_123 ✭✭✭✭✭✭

    Found a couple glitches in mine, and optimized it a bit

    =DATE(YEAR(Created@row), 1, 1) + ROUNDDOWN(((NETDAYS(DATE(YEAR(Created@row), 1, 1), Created@row) - 1) * 1440 + 60 * (VALUE(MID(Created@row, 10, LEN(Created@row) - 15)) + IF(AND(RIGHT(Created@row, 2) = "PM", NOT(VALUE(MID(Created@row, 10, LEN(Created@row) - 15)) = 12)), 12, 0) + 2) + VALUE(LEFT(RIGHT(Created@row, 5), 2))) / 1440) + " " + ROUNDDOWN(MOD(((NETDAYS(DATE(YEAR(Created@row), 1, 1), Created@row) - 1) * 1440 + 60 * (VALUE(MID(Created@row, 10, LEN(Created@row) - 15)) + IF(AND(RIGHT(Created@row, 2) = "PM", NOT(VALUE(MID(Created@row, 10, LEN(Created@row) - 15)) = 12)), 12, 0) + 2) + VALUE(LEFT(RIGHT(Created@row, 5), 2))), 1440) / 60) + ":" + LEFT(RIGHT(Created@row, 5), 2)

    I didn't use the find function and used the mid because there is only a variation of a single value, the hours, that can change the text length. The only part that find could have shortened is processing the minutes, as everything to the left of hours is static, so the location reference when using mid won't ever change.

    Mine posts out in a 24 hour clock as well. I am getting an error trying to post yours into my sheet for some reason though.

    · Share on FacebookShare on Twitter
  • L_123
    L_123 ✭✭✭✭✭✭

    https://app.smartsheet.com/b/publish?EQBCT=b5ff050c846e40869ec7fe07e10ebf3b

     

    You are welcome to copy whatever you like.

    · Share on FacebookShare on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    It was an issue with the formula in the New Date column. I wrote my solution out "on the fly" and hadn't tested the full thing.

     

    When I plugged it into a sheet, I got an #INVALID OPERATION ERROR in the [New Date] column.

    .

    =IF([Add Hours]@row >= 24, 1) + DATEONLY(Created@row)

     

    Produced the error, but flipping it around so that it was the date first

     

    =DATEONLY(Created@row) + IF([Add Hours]@row >= 24, 1)

    worked.

    .

    HERE is a published version.

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • firestorm
    firestorm ✭✭✭

    hi, how do I amend the formula to convert it to GMT+8?

    · Share on FacebookShare on Twitter
  • Wilchung
    Wilchung ✭✭
    edited 09/14/23

    Hi all, how do I change the formula to convert it by -15 (from GMT+8 to GMT -7)?

    because the time will become negative and need to change back the date, month and year...

    Thank you.

    · Share on FacebookShare on Twitter
  • allison.worley
    allison.worley ✭✭✭

    Based on the formulas above, how would you propose changing from CST to IST when the time change is not only hourly based but also minute reference as the time change is +10.5 hours?

    image.png


    · Share on FacebookShare on Twitter

Categories

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video