date format

[jay_sharma_909]

how do you format  "Tue, 13 Sep 2022 20:11:32 GMT" to  "mm/dd/yyyy h:mm" ? Thanks

[#2 · Accepted solution]

@jay_sharma_909 

You 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","")

[#3]

It worked perfectly. Thanks Hans :) Would you please explain what is that function telling? wanted to understand!

[#4]

@jay_sharma_909 

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","")

[#5]

yeah I was wondering what 1* was doing. now it makes perfect sense. I appreciate it again!!