Weird Date/Time formatting

Ronderbecke

Board Regular
Joined
Oct 4, 2017
Messages
73
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.
 
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.
 
Upvote 0

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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:0012/30/1899 0:00:00
2020-07-03T15:00:55-04:0012/30/1899 0:00:00
2020-07-07T14:00:43-04:0012/30/1899 0:00:00
2020-07-03T17:30:40-04:0012/30/1899 0:00:00
2020-07-08T10:00:47-04:0012/30/1899 0:00:00
2020-07-01T11:00:09-04:0012/30/1899 0:00:00
2020-07-01T18:30:02-04:0012/30/1899 0:00:00
2020-07-03T11:00:45-04:0012/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?
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
a pleasure to have helped and thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,560
Latest member
Torchwood72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top