LinkBack URL
About LinkBacks
How can I convert a timestamp in the format 2021-06-13T01:36:28Z (ISO 8601) to EST?
How can I convert a timestamp in the format 2021-06-13T01:36:28Z (ISO 8601) to EST?
Last edited by ccarmichael; 02-22-2022 at 05:09 PM.
With timestamp in A1
in B1
=DATEVALUE(LEFT(A1,10)) Date
in C1
=TIMEVALUE(MID(A1,12,8)) UCT
in D1 (EST)
=B1+C1-5/24 (following example in Ben's post below!!!)
Last edited by JohnTopley; 02-22-2022 at 05:43 PM.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
format: dddd mm/dd/yyyy hh:mm;@
Please Login or Register to view this content.
Ben Van Johnson
Big help, thank you!
I'm making a tool for college football season predictions. I found a source online for the schedules that has everything I need, EXCEPT the dates/times for all the games are in UTC.
My goal was to convert the UTC to local kickoff time, account for different time zones for 130+ FBS teams. There's a big difference between a team kicking off at 4pm their time vs 7pm. Home teams are nuclear hot for night games, especially if they haven't had one in a while. I also needed to account for Daylight Savings Time changes based on the game date, and account for the 3 teams that don't follow it (Hawai'i, Arizona State, Arizona)
I ended up combining this formula with some IF statements. It's a bunch of ratchet-*** Excel but hey if it works it works right?
Basically it does two IF statements for each time zone: does a VLOOKUP to find the Home Team's time zone, then two calculations: one for Standard Time and one for Daylight Savings Time (based on the game date).
I have no doubt there's a simpler way to do it but hey, it's done.
Attachment 840871
Attachment 840872
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks