Date / Time Extraction

[robb12]

Hi, and thanks for all help in advance.

I down load date and time information and it comes in a relatively difficult format.

2017-11-17T09:18:00

Could you please show me some good formulas to extract specific parts - ideally so I can get a separate date and separate time column?

Cheers
Cam

 

[Dim Me as xlNoob]

Is the format always exactly the same? It's easier to write a formula if it is. So the following formulae rely on a couple of rules:

1. The "T" always separates the date and time
2. Both the date and time ALWAYS have leading zeros (e.g. 2017-02-05T04:00:00)

Formula to extract date from cell A1: =DATE(LEFT(A1,4),MID(A1,6,2),MID(A1,9,2))
Formula to extract time from cell A1: =TIME(MID(A1,FIND("T",A1,1)+1,2),MID(A1,FIND("T",A1,1)+4,2),MID(A1,FIND("T",A1,1)+7,2))


Hope this helps.

 

[Rick Rothstein]

1. The "T" always separates the date and time
2. Both the date and time ALWAYS have leading zeros (e.g. 2017-02-05T04:00:00)

If your assumptions are correct, these simple formulas will work..

For the date: =0+LEFT(A1,10)

For the time: =0+RIGHT(A1,8)

These formulas will return the date/time serial numbers so the individual cell will have to be formatted with the desired date and time formats.