Skip to main content
deleted 14 characters in body
Source Link
Scott Craner
  • 23.4k
  • 3
  • 23
  • 26

Use this formula that creates a true date/time: REPLACE(LEFT(A1,LEN(A1)-1),11,1," "), adds 12 hours: +TIME(12,0,0) and then formats it back to the original format "yyyy-mm-dd\Thh:mm:ss.000\Z":


  1. Creates a true date/time: REPLACE(LEFT(A1,23),11,1," ")
  2. Adds 12 hours: +TIME(12,0,0)
  3. Then formats it back to the original format "yyyy-mm-dd\Thh:mm:ss.000\Z":
 
=TEXT(REPLACE(LEFT(A1,LEN(A1)-123),11,1," ")+TIME(12,0,0),"yyyy-mm-dd\Thh:mm:ss.000\Z")

enter image description hereenter image description here

Use this formula that creates a true date/time: REPLACE(LEFT(A1,LEN(A1)-1),11,1," "), adds 12 hours: +TIME(12,0,0) and then formats it back to the original format "yyyy-mm-dd\Thh:mm:ss.000\Z":

=TEXT(REPLACE(LEFT(A1,LEN(A1)-1),11,1," ")+TIME(12,0,0),"yyyy-mm-dd\Thh:mm:ss.000\Z")

enter image description here

Use this formula that:


  1. Creates a true date/time: REPLACE(LEFT(A1,23),11,1," ")
  2. Adds 12 hours: +TIME(12,0,0)
  3. Then formats it back to the original format "yyyy-mm-dd\Thh:mm:ss.000\Z":
 
=TEXT(REPLACE(LEFT(A1,23),11,1," ")+TIME(12,0,0),"yyyy-mm-dd\Thh:mm:ss.000\Z")

enter image description here

Source Link
Scott Craner
  • 23.4k
  • 3
  • 23
  • 26

Use this formula that creates a true date/time: REPLACE(LEFT(A1,LEN(A1)-1),11,1," "), adds 12 hours: +TIME(12,0,0) and then formats it back to the original format "yyyy-mm-dd\Thh:mm:ss.000\Z":

=TEXT(REPLACE(LEFT(A1,LEN(A1)-1),11,1," ")+TIME(12,0,0),"yyyy-mm-dd\Thh:mm:ss.000\Z")

enter image description here