[X] My Assistant

[djellison]

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

[helvick]

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

[brianc]

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

[djellison]

Wow - I'll try these later

Doug

[Leither]

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

[helvick]

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.

[nprev]

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!