# Display date and time in India time from GMT

Options

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)

Tags:

• Moderator
Options

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

• Moderator
Options

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

• Options

Hi Krissia,

Perfect simple solution, it works.

Thank you very much.

Have a nice day.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!