Converting string of dates in Excel

  • Thread starter Thread starter QuantumCurt
  • Start date Start date
  • Tags Tags
    Excel String
Click For Summary

Discussion Overview

The discussion revolves around converting a specific format of date strings in Excel for the purpose of plotting data related to beam intensity from the NuMI neutrino beam line at Fermilab. Participants explore various methods to manipulate and format these date strings, which include both technical Excel solutions and potential programming approaches.

Discussion Character

  • Technical explanation
  • Exploratory
  • Debate/contested
  • Homework-related

Main Points Raised

  • One participant describes the format of the date strings and expresses the need to convert them for plotting in Excel, noting the challenge posed by the large number of data points.
  • Another participant suggests using the RIGHT function in Excel to extract the time portion of the date strings, but acknowledges that this only works for a single cell.
  • There are discussions about checking cell formatting to ensure that the data is recognized as DateTime values rather than strings.
  • A participant proposes processing the data with a programming language if it is in a text file format, suggesting that this could facilitate conversion to a more Excel-friendly format.
  • Several participants provide instructions on how to apply formulas to multiple cells in Excel, including dragging the fill handle to extend the formula.
  • Concerns are raised about the x-axis of the plot still displaying numerical values instead of the expected time format, prompting further troubleshooting steps.
  • Participants discuss the type of chart being used (line vs. scatter) and how this affects the ability to edit axis labels and data ranges.
  • One participant mentions that the data range is too complex to be displayed, which complicates the process of editing the chart.

Areas of Agreement / Disagreement

Participants generally agree on the need to manipulate the date strings for plotting, but there is no consensus on the best method to achieve this. Multiple competing views and approaches are presented, and the discussion remains unresolved regarding the most effective solution.

Contextual Notes

Limitations include the specific format of the date strings, the potential complexity of the data range in Excel, and the varying levels of familiarity with Excel and programming among participants.

  • #31
With the text in column A, enter this in B1:
Code: 
=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:
Code: 
=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
Code: 
=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.
 
  • Like
Likes   Reactions: QuantumCurt
Physics news on Phys.org
  • #32
That seems to have worked out very well! Thank you much!
 

Similar threads

Fitting data to a Gaussian Distribution in Excel
  • · Replies 11 ·
Replies
11
Views
28K
High School Excel: converting a 3-ish week count into a monthly count
  • · Replies 15 ·
Replies
15
Views
2K
Solve FORTRAN 77 Programming Problem: Date Validation & Conversion
  • · Replies 5 ·
Replies
5
Views
3K