Converting string of dates in Excel

1. Jun 12, 2015

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!

2. Jun 12, 2015

cpscdave

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 whats 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 :)

3. Jun 12, 2015

QuantumCurt

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

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?

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!

4. Jun 12, 2015

Staff: Mentor

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...

5. Jun 12, 2015

cpscdave

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 wont change. vs$A1 only the number will change.

Hope that helps

6. Jun 12, 2015

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.

7. Jun 12, 2015

QuantumCurt

That did the trick! Thank you so much!

8. Jun 12, 2015

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.

9. Jun 12, 2015

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.

10. Jun 12, 2015

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.

11. Jun 12, 2015

cpscdave

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

12. Jun 12, 2015

cpscdave

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

13. Jun 12, 2015

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."

14. Jun 12, 2015

cpscdave

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

15. Jun 12, 2015

QuantumCurt

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

16. Jun 12, 2015

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.

Last edited: Jun 12, 2015
17. Jun 12, 2015

MrAnchovy

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)"

18. Jun 13, 2015

QuantumCurt

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

19. Jun 13, 2015

MrAnchovy

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.

20. Jun 13, 2015

QuantumCurt

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.