Display date and time in India time from GMT

Sreeranjith Premsekar

Assuming Created time is captured inGMT datetime, I would like to display Indian standard time in another column. I referend few timezone conversion samples, but its not working if the timezone time with excess of 30 min. India timezone is 5 hrs 30 min ahead of GMT, formulas discussed in the forum works if GMT+5 and not 5.5.

=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) + 5) + 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) + 5) + VALUE(LEFT(RIGHT(Created@row, 5), 2))), 1440) / 60) + ":" + LEFT(RIGHT(Created@row, 5), 2)

Krissia B.

Hello @Sreeranjith Premsekar

Upon further review and test on this, I used the formula: =Created@row + (5.5 / 24) + ""

The 5.5/24 will shift the time zone. Though anytime someone from another time zone opens the sheet it will update the created date and update the results of the formula.

See my test sheet here: https://app.smartsheet.com/b/publish?EQBCT=d0745284e1434484acd858952f541b2d

Let me know how it goes & if you have any additional questions!

Cheers,

Krissia

Krissia B.

Hello @Sreeranjith Premsekar

Upon further review and test on this, I used the formula: =Created@row + (5.5 / 24) + ""

The 5.5/24 will shift the time zone. Though anytime someone from another time zone opens the sheet it will update the created date and update the results of the formula.

See my test sheet here: https://app.smartsheet.com/b/publish?EQBCT=d0745284e1434484acd858952f541b2d

Let me know how it goes & if you have any additional questions!

Cheers,

Krissia

Sreeranjith Premsekar

Hi Krissia,

Perfect simple solution, it works.

Thank you very much.

Have a nice day.