Display date and time in India time from GMT
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)
Best Answer
-
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
Answers
-
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
-
Hi Krissia,
Perfect simple solution, it works.
Thank you very much.
Have a nice day.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!