1

I am trying to convert a date string field to a date. Problem is the string field has offset for Eastern time in it. Please suggest how to convert it to date time.

Date String: 2016-09-21T10:01:43-04:00

This has to be converted to valid Date Time in Excel.

user1760178
  • 6,277
  • 5
  • 27
  • 57
  • 3
    You could try this: https://stackoverflow.com/questions/4896116/parsing-an-iso8601-date-time-including-timezone-in-excel – zedfoxus Apr 30 '19 at 03:16
  • Might be worth asking from where the data is from? Usually the import/export wizards offer options for data conversions and date is often included so you don't have to figure how to convert them. – Jerry Apr 30 '19 at 05:39

1 Answers1

1

The sure fire way is to strip each of the elements you need from the string then place them in the appropriate spots in the DATE and TIME functions. There are shorter methods which use DATEVALUE and TIMEVALUE.

Grab the year

=LEFT(A1,4)

Grab the month

=MID(A1,6,2)

Grab the day

=MID(A1,9,2)

Grab the hour

=MID(A1,12,2)

Grab the minutes

=MID(A1,15,2)

Grab the seconds

=MID(A1,18,2)

Time adjustment hour

=MID(A1,21,2)

Time adjustment minutes

=RIGHT(A1,2)

Time adjustment direction

=MID(A1,20,1)

Now that you have stripped out all the elements which are all still strings, you can dump them into DATE(year,month,day) and TIME(Hour, Minutes, Seconds)

=DATE(LEFT(A1,4),MID(A1,6,2),MID(A1,9,2))+TIME(MID(A1,12,2),MID(A1,15,2),MID(A1,18,2))+TIME(MID(A1,21,2),RIGHT(A1,2),0)*if(MID(A1,20,1)="-",-1,1)

POC

NOTE: This answer assumes a leading 0 for single digits days

Forward Ed
  • 9,484
  • 3
  • 22
  • 52