Weird Date/Time formatting

[Ronderbecke]

Currently I have a program that uses google forms and sends the information to a sheet. The date/time of the appointment comes in formatted like this:

2020-07-03T15:00:55-04:00

I have been looking for a solution on how to convert that (any formula possible) to make it look more like: 7/3/2020 3:00 PM

Is that even possible with the structure of that date/time? I haven't seen anything formatted that way before.

 

[Fluff]

In that case you should always state that, as Excel & Sheets are not the same.
You should also have posted this in the General Discussion section.

 

[Ronderbecke]

I am not sure I made this work quite right? When converting it didn't process through correctly and formatting the new column to date/time shows this:

2020-07-03T13:30:52-04:00	12/30/1899 0:00:00
2020-07-03T15:00:55-04:00	12/30/1899 0:00:00
2020-07-07T14:00:43-04:00	12/30/1899 0:00:00
2020-07-03T17:30:40-04:00	12/30/1899 0:00:00
2020-07-08T10:00:47-04:00	12/30/1899 0:00:00
2020-07-01T11:00:09-04:00	12/30/1899 0:00:00
2020-07-01T18:30:02-04:00	12/30/1899 0:00:00
2020-07-03T11:00:45-04:00	12/30/1899 0:00:00

I used this formula: =ARRAYFORMULA(LEFT(SUBSTITUTE('Form Responses 1'!L2:L,"T",""),18)+0)

Does the arrayformula cause an issue in the adjustment?

It appears something weird was going on because I reformatted with date and time, but it actually shows this:

7/3/2020 1:30 PM

7/3/2020 3:0 PM

7/7/2020 2:0 PM

7/3/2020 5:30 PM

7/8/2020 10:0 AM

7/1/2020 11:0 AM

7/1/2020 6:30 PM

7/3/2020 11:0 AM

Any one know why it would cut off a 0 after the first one but only on some of them?

 

[Ronderbecke]

In that case you should always state that, as Excel & Sheets are not the same.
You should also have posted this in the General Discussion section.

Sorry about that, I did not realize that. Is there a way to move it?

 

[Fluff]

Is there a way to move it?

Already done

 

[pjmorris]

The custom format that you've applied is "d/m/yyyy h:m AM/PM" and so only one digit is used for the minutes if it can get away with it! Change the format string to: "d/m/yyyy h:mm AM/PM" should correct it.

Additionally my formula is slightly wrong it should read: =LEFT(SUBSTITUTE(A1,"T"," "),19)+0 it only affects whether the seconds are converted correctly so you'd probably not notice.

HTH

 

[Ronderbecke]

The custom format that you've applied is "d/m/yyyy h:m AM/PM" and so only one digit is used for the minutes if it can get away with it! Change the format string to: "d/m/yyyy h:mm AM/PM" should correct it.

Additionally my formula is slightly wrong it should read: =LEFT(SUBSTITUTE(A1,"T"," "),19)+0 it only affects whether the seconds are converted correctly so you'd probably not notice.

HTH

Wonderful, strange that it didn't work until i reformatted to a different date/time and then went back and added it. Thanks so much for your help on that, it worked great!

 

[pjmorris]

a pleasure to have helped and thanks for the feedback.