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.

 

[gaz_chops]

If the structure is always like that, try

=TEXT(DATE(LEFT(A2,4),MID(A2,6,2),MID(A2,9,2)),"dd/mm/yy")&" "&TEXT(REPLACE(RIGHT(A2,14),9,99,""),"hh:mm am/pm")

 

[pjmorris]

presumably the 04:00 refers to the duration? what do you want to happen to that?

To convert the first part to a date time, use: =DATE(LEFT(A1,4),MID(A1,6,2),MID(A1,9,2))
To convert the start time to a time, use: =TIME(MID(A1,12,2),MID(A1,15,2),MID(A1,18,2))
To convert the duration to a time, use: =RIGHT(A1,5)+0 and format as a time (although excel converts it correctly it doesnt change the cell format).

To get Date & Time in the same cell, and ignore the duration, try: =LEFT(SUBSTITUTE(A4,"T"," "),18)+0 and set the custom format to d/m/yyyy h:mm AM/PM

The difference between gaz's solution and mine is that gaz's produces the conversion as text, whereas mine is a date/time - it just depends on what you want.

Another way of doing it would be to convert the data using PowerQuery - very powerful, but more to learn!

 

[Ronderbecke]

If the structure is always like that, try

=TEXT(DATE(LEFT(A2,4),MID(A2,6,2),MID(A2,9,2)),"dd/mm/yy")&" "&TEXT(REPLACE(RIGHT(A2,14),9,99,""),"hh:mm am/pm")

View attachment 17377

That works perfectly. Working on trying to convert it so that it works all the way down L2:L and converts all of them. Thank you so much gaz_chops

 

[gaz_chops]

To get Date & Time in the same cell, and ignore the duration, try: =LEFT(SUBSTITUTE(A4,"T"," "),18)+0 and set the custom format to d/m/yyyy h:mm AM/PM

Neat

 

[gaz_chops]

That works perfectly. Working on trying to convert it so that it works all the way down L2:L and converts all of them. Thank you so much gaz_chops

You're welcome.

 

[Ronderbecke]

presumably the 04:00 refers to the duration? what do you want to happen to that?

To convert the first part to a date time, use: =DATE(LEFT(A1,4),MID(A1,6,2),MID(A1,9,2))
To convert the start time to a time, use: =TIME(MID(A1,12,2),MID(A1,15,2),MID(A1,18,2))
To convert the duration to a time, use: =RIGHT(A1,5)+0 and format as a time (although excel converts it correctly it doesnt change the cell format).

To get Date & Time in the same cell, and ignore the duration, try: =LEFT(SUBSTITUTE(A4,"T"," "),18)+0 and set the custom format to d/m/yyyy h:mm AM/PM

The difference between gaz's solution and mine is that gaz's produces the conversion as text, whereas mine is a date/time - it just depends on what you want.

Another way of doing it would be to convert the data using PowerQuery - very powerful, but more to learn!

Thanks Peter, I think getting the date/time might be easier in what im ultimately trying to do with this formula. It's google sheets connected to a form so I need it to update the whole column on the new sheet.

 

[Ronderbecke]

presumably the 04:00 refers to the duration? what do you want to happen to that?

To convert the first part to a date time, use: =DATE(LEFT(A1,4),MID(A1,6,2),MID(A1,9,2))
To convert the start time to a time, use: =TIME(MID(A1,12,2),MID(A1,15,2),MID(A1,18,2))
To convert the duration to a time, use: =RIGHT(A1,5)+0 and format as a time (although excel converts it correctly it doesnt change the cell format).

To get Date & Time in the same cell, and ignore the duration, try: =LEFT(SUBSTITUTE(A4,"T"," "),18)+0 and set the custom format to d/m/yyyy h:mm AM/PM

The difference between gaz's solution and mine is that gaz's produces the conversion as text, whereas mine is a date/time - it just depends on what you want.

Another way of doing it would be to convert the data using PowerQuery - very powerful, but more to learn!

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?

 

[Fluff]

Are you doing this in Google sheets?
arrayformula does not exist in Excel.

 

[Ronderbecke]

Are you doing this in Google sheets?
arrayformula does not exist in Excel.

Yes, this is all being done in google sheets