This is a date/time format I often come across when looking a spaceflight related data from places such as the PDS or the radiation / relay logs...
2004-01-02T19:15:38 or 2006-315T23:03:23.0(one using yyyy-mm-dd and one using yyyy-ddd etc )

Try as I might, I can't get Excel to 'read' these properly, even using my own custom cell format type as yyyy-ddd"T"hh:mm:ss.s

Any pointers?

Doug

When I come across issues like this I usually "clean" up the data using Perl but that's not an option for most folks.

Within Excel itself you could do the following for the first format:
Col A - Imported String value (2004-01-02T19:15:38)
Col B - =Left(A1,10) (== 2004-01-02)
Col C - =DateValue(B1) (==37988)
Col D - =Right(A1,9) (==19:15:38)
Col E - =TimeValue(D1) (==0.802523148)
Col F - =C1+E1.
you might have to set the default date format appropriately first or build the Date string into YYY-MM-DD first to ensure the Month\Day order format is correct.

for the second format (2006-315T23:03:23.0)
Col A - Imported String value (2004-01-02T19:15:38)
Col B - =Left(A1,4) (== 2004)
Col C - =mid(A1,6,3) (==315)
Col D - =Date(B1,1,1)-1 (31/12/2005)
Col E -=D1+C1
Then proccess the time as before and add it to the date value.

You would probably carry out all of the above in a single cell but I've broken it out for clarity.

Of course I'm assuming that the data\time formats use a fixed string length for each data item (e.g. Day 4 of the year in the second format is represented by 004). That's why I'd use Perl by preference.

2004-01-02T19:15:38
2004-01-02T19:15:38

Doug

Copy your Date / Time string into Column A e.g 2004-01-02T19:15:38 into cell A1

Cells in Column B should have formula =DATE(MID(A1,1,4),MID(A1,6,2),MID(A1,9,2))
This rewolves the Date Part

Cells in Column C should have the formula =TIME(MID(A1,12,2),MID(A1,15,2),MID(A1,18,2))

I think that should do that you want assuming the Date / Time strings are all fixed length

Regards


Brianc

Wow - I'll try these later

Doug

Doug


For the second format e.g 2006-315T23:03:23.0

In Column B use formula =DATE(LEFT(A1,4),1,1)+MID(A1,6,3)-1

and in

Column C use formula =TIME(MID(A1,10,2),MID(A1,13,2),MID(A1,16,2))

As Brianc states, these assume the Date / Time strings are all fixed length, if not you'll have to use the LEN function.

Aye

The problem with dealing with potentially variable length date items got me thinking that there is a simpler way:
Col A - Imported String value (2004-01-02T19:15:38)
Col B - = VALUE(SUBSTITUTE(A1,"T"," ")) (convert to the correct date and time)
Provided your default date format's year/month/day dequence order matches the input data this will work.

Sure glad to see that there are some Excel whizzes here. I'm taking a class (systems optimization) that is almost literally killing me with novel Excel applications...might ask you guys for some pointers as well!