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
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!