Change Time Format In Excel From Exported Data

[JEF13]

When exporting a data set into Excel the time appears as:

2023-02-23T19:34:58-05:00
2023-02-24T19:58:42-05:00
2023-02-25T20:44:14-05:00
2023-02-26T20:08:51-05:00
2023-02-27T19:54:45-05:00

When I attempt to format using hh:mm AM/PM nothing happens.

How can I convert the above to hh:mm format for all cells?

 

[rlv01]

When exporting a data set into Excel the time appears as:

When you move data into excel, that's usually refered to as importing. While moving data out of excel is exporting. Can I assume you mean importing?

When I attempt to format using hh:mm AM/PM nothing happens

Excel can, to a limited extent figure, out when string data imported from say, a CSV file is a date or a time and automatically do the conversion, but this:

Book1
	A
1	2023-02-23T19:34:58-05:00
2	2023-02-24T19:58:42-05:00
3	2023-02-25T20:44:14-05:00
4	2023-02-26T20:08:51-05:00
5	2023-02-27T19:54:45-05:00
Sheet1

is non-standard. You will need to convert it before import, or convert it after import using a formula

Book1
	A	B
1	2023-02-23T19:34:58-05:00	02-23-23 07:34 PM
2	2023-02-24T19:58:42-05:00	02-24-23 07:58 PM
3	2023-02-25T20:44:14-05:00	02-25-23 08:44 PM
4	2023-02-26T20:08:51-05:00	02-26-23 08:08 PM
5	2023-02-27T19:54:45-05:00	02-27-23 07:54 PM
Sheet1

Cell Formulas
Range	Formula
B1:B5	B1	=DATEVALUE(LEFT(A1,10))+TIMEVALUE(MID(A1,12,8))

(I'm ignoring "-05:00" since you have not explained it).

 

[etaf]

what are you exporting from???

I have seen this from systems where it uses the UTC (GMT) time and then adds or subtracts the hour difference for its location , often used in global call handling systems
so the one database is feed from systems all over the world and so have to take into account any time differences

UTC
Coordinated Universal Time or Universal Time Coordinated
used to be GMT timezone London

-5 - is that US - EST Eastern Standard time - on East side of USA

 

[Saba Sabaratnam]

Another solution may be to find and replace both T and -05:00 without using formulas.

Kind regards

Saba

 

[Saba Sabaratnam]

Replace T with a space and -05:00 with a blank

 

[JEF13]

Thanks everyone for yoru reply. To clariy, I have no control over the data that is being saved into a .csv or excel file as it's coming from another site. I'm looking for a formula that I can copy and paste for thousands of cells. The goal is to take 2023-02-23T19:34:58-5:00 and have it show:

2023 02 23 19:34 which is for February 23, 2023 at 7:34 PM.

Thanks.

Jeffrey

 

[MARK858]

Maybe the below. Format the cells as yyyy mm dd hh:mm

Book1.xlsb
	A	B
2	2023-02-23T19:34:58-5:00	2023 02 23 19:34
Sheet2

Cell Formulas
Range	Formula
B2	B2	=--SUBSTITUTE(LEFT(A2,19),"T"," ")

 

[rlv01]

The goal is to take 2023-02-23T19:34:58-5:00 and have it show: 2023 02 23 19:34 which is for February 23, 2023 at 7:34 PM.

Doesn't the formula I posted above already do that?

Book1
	A	B
1	2023-02-23T19:34:58-05:00	February 23, 2023 07:34 PM
Sheet3

Cell Formulas
Range	Formula
B1	B1	=DATEVALUE(LEFT(A1,10))+TIMEVALUE(MID(A1,12,8))

 

[etaf]

are you using that time to then workout any stats - as i mentioned just beware , that is not the real time of the event , as mentioned the -5 gives the time difference
i used that in the 90's for all sorts of global stats and performance issue on a central database / warehouse

 

[JEF13]

Maybe the below. Format the cells as yyyy mm dd hh:mm

Book1.xlsb
	A	B
2	2023-02-23T19:34:58-5:00	2023 02 23 19:34
Sheet2

Cell Formulas
Range	Formula
B2	B2	=--SUBSTITUTE(LEFT(A2,19),"T"," ")

Thank you. This works.