Forum Discussion
Need To Convert Data In Cell To Date and Time
Hello, normally I am pretty good at writing my own formulas, but for some reason this one has me a little stumped. We have a website with forms that clients enter information in, and that info is setup to automatically enter that data into a spreadsheet we have in our Google folder. What I need is a formula to convert the raw data into a format we can use. The cell below is what I am hoping to build a formula for. First, the date and time is there... However, here is the challenge... it is in Zulu Time which is 8 hours ahead. So I need help writing a formula to give me as follows...
A1 A2
Is there ANY way this can be done with a formula? Thank you so much for your help in advance. I do sincerely appreciate it.
As variant
=LEFT(A1,10) + MID(A1,12,10) + MID(A1,21,3)/24/60/60/1000 - 8/24
As variant
=LEFT(A1,10) + MID(A1,12,10) + MID(A1,21,3)/24/60/60/1000 - 8/24You are welcome
Thank you so much! You are amazing! I was even able to modify it so we could change the date to a more standard format. I do appreciate it!
Hi
=LET( value, "2024-12-03T03:05:29.062Z", date, TEXTBEFORE( value, "T" ), time, TEXTBEFORE( TEXTAFTER( value, "T" ), "." ), ( date + time ) - TIME( 8,0,0 ) )Does this do what you expect?
How would you identify the cell the data is in? In other words, if the data was in cell A1 and needed to be copied to A2 in the new format... where would we insert that at?
=LET( value, A1, date, TEXTBEFORE( value, "T" ), time, TEXTBEFORE( TEXTAFTER( value, "T" ), "." ), ( date + time ) - TIME( 8,0,0 ) )
