Sep 27 2022 12:08 PM
how do you format "Tue, 13 Sep 2022 20:11:32 GMT" to "mm/dd/yyyy h:mm" ? Thanks
Sep 27 2022 01:15 PM
SolutionYou cannot simply format such a value as date/time since Excel does not recognize it as a date/time.
Let's say you have such a value in A2.
Enter the following formula in B2, and format B2 as mm/dd/yyyy h:mm.
You can fill this down if required.
=1*SUBSTITUTE(MID(A2,6,50)," GMT","")
Sep 28 2022 06:11 AM
Sep 28 2022 06:25 AM
MID(A2,6,50) removes the first 5 characters from the string: in your example the "Tue, " part. (50 is just an arbitrary number larger than the length of the string). So we are left with
3 Sep 2022 20:11:32 GMT
SUBSTITUTE(MID(A2,6,50)," GMT","") removes " GMT" from the end, so we now have
3 Sep 2022 20:11:32
Excel is able to recognize this text string as a date/time value. We just have to force it into a number (Excel stores dates and times as numbers). One way to do that is to multiply the value with 1. This may seem strange, but when we multiply, Excel tries its best to treat the value supplied as a number. We could also have used
=SUBSTITUTE(MID(A2,6,50)," GMT","")+0
or
=--SUBSTITUTE(MID(A2,6,50)," GMT","")
Sep 28 2022 06:36 AM
Sep 27 2022 01:15 PM
SolutionYou cannot simply format such a value as date/time since Excel does not recognize it as a date/time.
Let's say you have such a value in A2.
Enter the following formula in B2, and format B2 as mm/dd/yyyy h:mm.
You can fill this down if required.
=1*SUBSTITUTE(MID(A2,6,50)," GMT","")