aashish1111
New Member
"2020-09-01 10:23:38Z" I have date time in this format in a column I want this entire column datetime format changed from UTC to IST Here are some values:
D F
TimeStamp(UTC) TimeStamp(IST)
2020-09-01 10:23:38Z 2020-09-01 15:53:38Z
2020-09-01 10:23:31Z 2020-09-01 15:53:31Z
2020-09-01 10:22:18Z 2020-09-01 15:52:18Z
Since IST is 5 hours 30 minutes ahead of UTC every value of column F needs to be ahead of D by 5 hours 30 minutes
Here are the list of things I have tried:
=D2+TIME(5,30,0)
=D2+TIMEVALUE("5:30")
=D2+5.5/24
=DATE(MID(D2,12,4),MONTH(1&MID(D2,5,3)),MID(D2,9,2))+(-6*3600+MID(D2,17,2)*3600+MID(D2,20,2)*60+MID(D2,23,2))/(24 * 3600)
All of the the above leads to #VALUE! error
I want the result to look like F column mentioned in example. I am looking for a formula to do that.
D F
TimeStamp(UTC) TimeStamp(IST)
2020-09-01 10:23:38Z 2020-09-01 15:53:38Z
2020-09-01 10:23:31Z 2020-09-01 15:53:31Z
2020-09-01 10:22:18Z 2020-09-01 15:52:18Z
Since IST is 5 hours 30 minutes ahead of UTC every value of column F needs to be ahead of D by 5 hours 30 minutes
Here are the list of things I have tried:
=D2+TIME(5,30,0)
=D2+TIMEVALUE("5:30")
=D2+5.5/24
=DATE(MID(D2,12,4),MONTH(1&MID(D2,5,3)),MID(D2,9,2))+(-6*3600+MID(D2,17,2)*3600+MID(D2,20,2)*60+MID(D2,23,2))/(24 * 3600)
All of the the above leads to #VALUE! error
I want the result to look like F column mentioned in example. I am looking for a formula to do that.