Converting string of dates in Excel

[QuantumCurt]

Hey everyone,

This may not be the right place to post this, so if an admin thinks a different section would be better suited, please move this thread.

So I'm in the midst of my summer internship at Fermilab right now. I'm currently trying to plot some data for the beam intensity of the NuMI neutrino beam line. I have long strings of dates of the form (day, month, date, clock time [to thousandths of a second]). For example:

##t_0##=Thu Jun 11 22:04:00.478 and ##t_{final}##=Thu Jun 11 23:03:59.142

When I plot this data in Excel it gets converted to numerical values ranging from 0 to ~2400. What I need to do is drop the day/month/date portion and convert my x-axis to showing values in clock time. Doing this manually isn't an option because I have upwards of 50,000 data points in some of these strings. The various formulas I've found for doing this don't really match up with the date format I have here, so I'm a bit lost.

Any help would be very appreciated!

 

[cpscdave]

##t_0##=Thu Jun 11 22:04:00.478 and ##t_{final}##=Thu Jun 11 23:03:59.142

There are 3 potential solutions:
1) 50000 isn't very much quit being lazy :P
2) Assuming that the DateTimes are in column A put in free column the following formula =RIGHT(A1,12). This will give you a substring of what's in cell A1, giving you the RIGHT most 12 characters. Depending of what you are trying to do you can mix it with LEFT which does the same thing, but gives you the LEFT most characters.
3) Check what the formatting on the cells are. If they are stored as DateTime values as opposed to strings you might be able to just change the cell formatting to a format that will work better.

Hope that helps :)

 

[QuantumCurt]

There are 3 potential solutions:
1) 50000 isn't very much quit being lazy :P

Can you email my mentor and just let him know that I'm going to have to spend the entire summer converting these cells?

2) Assuming that the DateTimes are in column A put in free column the following formula =RIGHT(A1,12). This will give you a substring of what's in cell A1, giving you the RIGHT most 12 characters. Depending of what you are trying to do you can mix it with LEFT which does the same thing, but gives you the LEFT most characters.

This worked! But only for the one cell. Forgive my newbiness, but how do I apply this to the whole string rather than the one cell?

3) Check what the formatting on the cells are. If they are stored as DateTime values as opposed to strings you might be able to just change the cell formatting to a format that will work better.

Hope that helps :)

I changed the format and it didn't seem to actually change anything. But my working knowledge of Excel is a bit limited.

Thanks for the help!

 

[berkeman]

There's another column for each entry with the actual data for that time, right?

Is the source data in Excel, or in some other format that you are importing into Excel?

If it's in a text file, you could consider processing that file with C or Tcl or whatever your favorite programming language is, to put the information into a more Excel-friendly format. The resulting file could be in *.csv format, for example, with the data nicely arranged and converted.

If the data is given to you in Excel format, then consider exporting that to a *.csv file, doing the formatting with your program, and re-importing it back into Excel...

 

[cpscdave]

This worked! But only for the one cell. Forgive my newbiness, but how do I apply this to the whole string rather than the one cell?

Click on the cell where you put the formula. The cell will highlight and there will be a small black box on the lower right corner. Click and hold that and "Drag" down as far as you want the formula.
When you drag up/down excel will update the formulas NUMBER value accordingly. IE it will change A1 to A2, A3, ETC
If you drag left/right it will update the the LETTER. A1 -> B1, C1 etc

If you don't want a value to change put a $ before the value. EG $A$1 won't change. vs $A1 only the number will change.

Hope that helps

 

[QuantumCurt]

I'm importing the data from ACNET (Accelerator Control Network here at Fermilab) as an Excel file, and then opening it directly in Excel.

I currently don't have a favorite programming language unfortunately. This summer is really my first experience with any programming. I'm taking an introductory programming course in the fall though, and following it up with a numerical analysis course in the spring. I'm teaching myself a bit of Python this summer (or at least trying to), but I haven't really gotten much beyond 'Hello, World!' yet at this point...lol

What I have is three sets of data. Beam intensity, and measured beam intensity at different points in the beam line. So I have three distinct domains and ranges being plotted on one chart.

 

[QuantumCurt]

Click on the cell where you put the formula. The cell will highlight and there will be a small black box on the lower right corner. Click and hold that and "Drag" down as far as you want the formula.
When you drag up/down excel will update the formulas NUMBER value accordingly. IE it will change A1 to A2, A3, ETC
If you drag left/right it will update the the LETTER. A1 -> B1, C1 etc

If you don't want a value to change put a $ before the value. EG $A$1 won't change. vs $A1 only the number will change.

Hope that helps

That did the trick! Thank you so much!

 

[QuantumCurt]

Okay, maybe it didn't. This let me convert the columns to just the time, but when I plot it it's still just giving me a range of numerical values from 0-~1400 on the x-axis.

 

[cpscdave]

Easy solution!
Right click the chart.
The "Horizontal (Category) Axis Labels" whill be
0, 1, 2, 3 etc

Click on edit
This will give you an "Axis Labels" window.
Beside the text box "Axis label range" is a button with an icon. Click that and it'll return you to the work book. Select the time range column and click that button again.

That should fix it.

 

[QuantumCurt]

When I right click the chart I'm not getting an option for "Horizontal Axis Labels".

If I right click the x-axis I get an option for 'Format axis' which gives me fields for minimum and maximum bounds, but it doesn't seem to be letting me change it.

 

[cpscdave]

What type of chart did you insert?
Line or Scatter?

 

[cpscdave]

Sorry Right click the chart and select "Select Data" first

 

[QuantumCurt]

It's a line chart.

Okay, I went to select data and I'm seeing the screen you're referring to. However, it isn't letting me click on 'edit.' The button is just greyed out.

ETA - It's also telling me that "The data range is too complex to be displayed. If a new range is selected, it will replace all of the series in the Series panel."

 

[cpscdave]

It's a line chart.

Okay, I went to select data and I'm seeing the screen you're referring to. However, it isn't letting me click on 'edit.' The button is just greyed out.

Its likely that its a scatter line chart :) They look the same just are handled differently by excel.

Instead on the left select the data series (there should only be 1 based on what you've said)and click "Edit"

Here you'll get a window with 3 boxes. Series Name, Seires X Values and Series Y Values.
Click the bunnon next to X Values and try what I suggested before. You'll likely see that it currently has the orignal values selected not the substring ones

 

[QuantumCurt]

Its likely that its a scatter line chart :) They look the same just are handled differently by excel.

Instead on the left select the data series (there should only be 1 based on what you've said)and click "Edit"

Here you'll get a window with 3 boxes. Series Name, Seires X Values and Series Y Values.
Click the bunnon next to X Values and try what I suggested before. You'll likely see that it currently has the orignal values selected not the substring ones

Yes, it's a scatter plot with connecting lines.

This is exactly what I did originally. I just copy/pasted the data from the spreadsheet into the Series X and Series Y boxes. The data seems to have been plotted just fine, the labels on the x-axis just aren't correct. It's for some reason assigning a numeric value between 0 and 1400 rather than displaying a time of hh:mm:ss.sss

 

[QuantumCurt]

So I added a custom format to give me labels of "hh:mm:ss.000" and now my x-axis labels are just all saying 00:00:00.000, even though the data all have distinct values that are definitely no 0. I asked my mentor and he didn't know how to change it either.

It seems really odd that I'm having so much trouble with this. This cannot be an uncommon usage of Excel lol

ETA - I set up another sheet with just the first ten data points so I could see how it's behaving. What it's doing is just assigning a number to each point. The first point is 1, the second point was 2, etc.

I guess what I need to figure out is how to change the x-values to the actual data in the cells. It seems incredibly bizarre to me that this isn't working. Excel has always just used whatever values I put into the cells whenever I've used it in the past.

 

[pbuk]

In that case you don't have a scatter chart, you have a line chart. Right-click on the chart, select "Change Chart Type" and select "X Y (Scatter)"

 

[QuantumCurt]

I don't want a scatter plot though. I need a scatter plot with straight connecting lines, which is what I have.

 

[pbuk]

I don't want a scatter plot though. I need a scatter plot with straight connecting lines, which is what I have.

No, you have a what Excel calls a "Line Chart". You do indeed want a scatter chart (which Excel calles an "X Y (Scatter) Chart"), and when you select "Change Chart Type" you can choose the format with lines or add them afterwards.

 

[QuantumCurt]

No, you have a what Excel calls a "Line Chart". You do indeed want a scatter chart (which Excel calles an "X Y (Scatter) Chart"), and when you select "Change Chart Type" you can choose the format with lines or add them afterwards.

I just checked again, and the graph type I selected is indeed called "Scatter Plot with Straight Connecting Lines."

In either case, are you thinking that switching to a scatter plot (without straight connecting lines) would resolve this issue? I tried it yesterday and it didn't make any difference aside from removing the straight connecting lines.

 

[pbuk]

I just checked again, and the graph type I selected is indeed called "Scatter Plot with Straight Connecting Lines."

In that case it seems you have not selected the x-axis data: this should be "Series 1" with the y-axis data "Series 2".

 

[QuantumCurt]

This is exactly what I've done every time I've tried it.

I tried manually entering a few of the first points into a new chart, and it came up with the correct values on the x-axis. I'm guessing it's related to how I converting the time formatting.

 

[pbuk]

If you are slicing up strings to use as values you need to use e.g. =value(mid(C3,12,5))

 

[pbuk]

... but if you are working with times it is probably more difficult than that. Look at the time(), date(), timevalue() and datevalue() functions.

 

[pbuk]

If you post some sample data

as code

I can use it to explain (probably by doing it).

 

[QuantumCurt]

If you post some sample data

as code

I can use it to explain (probably by doing it).

Sorry for the delayed reply. It was a busy weekend.

Here's some sample data. The first column is the date and the time (in military time), and the second column is beam intensity.

Fri Jun 12 10:03:43.278        32.18554159
Fri Jun 12 10:03:44.611        32.07059549
Fri Jun 12 10:03:45.944        32.04795459
Fri Jun 12 10:03:47.277        32.26913876
Fri Jun 12 10:03:48.610        31.88946829
Fri Jun 12 10:03:49.943        32.03924655
Fri Jun 12 10:03:51.275        31.98873993
Fri Jun 12 10:03:52.608        32.01834726
Fri Jun 12 10:03:53.941        31.87205221
Fri Jun 12 10:03:55.274        31.98873993

I then used the formula =RIGHT(A1,12) to keep only the right 12 digits. I'm thinking this is where things go wrong, because if I manually enter a sample of data points, they come up fine.

I need the time in hh:mm:ss.000, or like this:

10:03:43.278       32.18554159
10:03:44.611       32.07059549
10:03:45.944       32.04795459
10:03:47.277       32.26913876
10:03:48.610       31.88946829
10:03:49.943       32.03924655
10:03:51.275       31.98873993
10:03:52.608       32.01834726
10:03:53.941       31.87205221
10:03:55.274       31.98873993

Any help would be greatly appreciated.

 

[cpscdave]

After trying out on your data I have discovered you cannot have dates or datetimes or times as column values for an excel scatter chart.

You'll have to convert to an explict line chart, which of course won't allow you to use all the fancy scatter plot functionalities.
I suppose it makes some sense since it doesn't make sense to do a "Best fit" line on a time value.

 

[QuantumCurt]

After trying out on your data I have discovered you cannot have dates or datetimes or times as column values for an excel scatter chart.

You'll have to convert to an explict line chart, which of course won't allow you to use all the fancy scatter plot functionalities.
I suppose it makes some sense since it doesn't make sense to do a "Best fit" line on a time value.

I just tried it with a standard line chart and it did indeed come up with the time values, so I guess I'll just have to stick with that. I don't think I necessarily need all of the scatter plot functionality here, so this should work out fine. Thank you so much! I'm feeling much better now that this is resolved.

However, this is odd. If I take a handful of these values and type the times in manually rather than trying to convert them, the chart comes up with the time as the x-axis values with the proper labels.

 

[cpscdave]

However, this is odd. If I take a handful of these values and type the times in manually rather than trying to convert them, the chart comes up with the time as the x-axis values with the proper labels.

With the scatter plot chart? The one thing you'll learn with Excel is that the only consistent thing about it is its inconsistency

 

[QuantumCurt]

With the scatter plot chart? The one thing you'll learn with Excel is that the only consistent thing about it is its inconsistency

Yes, if I manually enter the points I can bring up a scatter plot that displays the actual time values on the x-axis.

I'll keep this inconsistency in mind.

 

[pbuk]

With the text in column A, enter this in B1:

=DATE(2015,MONTH(DATEVALUE("1 "&MID(A1,5,3))),VALUE(MID(A1,9,2)))+TIMEVALUE(MID(A1,12,8))+VALUE(MID(A1,21,3)/24/60/60/1000)

... and this in C1:

=VALUE(RIGHT(A1,11))

... and copy the formulas down.

This should work, but you will have to play with the x-axis options a bit: you want to set the "Major unit" to 1.15471e-5 (this is the way Excel stores the value 1 s) and with these data set the "Minimum" to 42167.419247685 (this is the value of 10:03:43 on 12 Jun 2015).

If you don't care about what it displays on the axis, it would be easier to ignore the day and just use

=TIMEVALUE(MID(A1,12,8))*24*60*60+VALUE(MID(A1,21,3)/1000)

to get values in seconds (to 1/1000 s) past midnight.

 

[QuantumCurt]

That seems to have worked out very well! Thank you much!