Yeah that’s tricky, especially since you are giving it a moving target by not having a date and time in a consistent timezone. Text to Date is not something I’ve ever needed to use. I think the Text to Date column assumes you are giving it a local time and is trying to give you GMT time as a result based on your local timezone, unless you specify a timezone which adjusts it from that GMT time.
When you don’t have a Z at the end of your date and time, I think it’s applying the local offset twice. 5 hours to calculate to GMT and another 5 hours to calculate back??? Doesn’t quite make sense to me why it does that.
When I add a Z to the end, then I get something more predictable. I still get GMT, which is 5 hours ahead of my local time, which makes more sense.
I think I understand what you are trying to accomplish and I’ve spent a fair bit of time trying to figure it out myself. I feel like I’m missing something obvious, but I’ve come up with a hacky workaround. This honestly doesn’t make logical sense to me, but it seems to work.
Create the following two columns.
Which looks like this.
Then use the OffsetFlip in your Text to Date column.
This is what I get as a result.
This is admittedly a dumb solution, but it’s something usable at least.