Converting string of dates in Excel

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

The discussion focuses on converting a string of dates in Excel, specifically from the format "Thu Jun 11 22:04:00.478" to a usable time format for plotting. Users suggested using the Excel formula =RIGHT(A1,12) to extract the last 12 characters, which successfully isolates the time portion. However, issues arose when plotting the data, as the x-axis displayed numerical values instead of the intended time format. The conversation highlighted the importance of ensuring the correct chart type is selected and the proper x-axis data range is set for accurate representation.

PREREQUISITES
  • Familiarity with Excel formulas, particularly string manipulation functions like RIGHT and MID.
  • Understanding of Excel chart types, specifically Line and Scatter plots.
  • Knowledge of date and time formatting in Excel.
  • Basic data handling skills, including importing and exporting data in CSV format.
NEXT STEPS
  • Learn how to use Excel's DATEVALUE and TIMEVALUE functions for converting string dates to Excel date formats.
  • Research how to format axes in Excel charts to display time values correctly.
  • Explore advanced Excel string manipulation techniques for more complex data extraction.
  • Investigate the use of VBA (Visual Basic for Applications) in Excel for automating data conversion tasks.
USEFUL FOR

Data analysts, Excel users working with time series data, and anyone involved in data visualization or reporting in Excel.

  • #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