Conversion of timezones (Solved Formula Included)

L_123

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)

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.

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

Anthony Barthelemy

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

 

Thanks

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.

L_123

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

 

You are welcome to copy whatever you like.

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.

firestorm

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

Wilchung

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.

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?