Forum Discussion
Solved
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 set...
As variant
=LEFT(A1,10) + MID(A1,12,10) + MID(A1,21,3)/24/60/60/1000 - 8/24
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 ) )
