CODEWITHSUNDEEP
exceldate
sharkonaut
sharkonaut

Reputation: 35

How can I convert a string (YYYY.MM) to a date format (MM/YYYY) that excel will understand for graphing purposes?

Currently my excel sheet has columns of strings in the Date column (example of 1871.01, 1871.02 etc). I would like to convert these to MM/YYYY date format so that I may chart the data in a time series format. I've tried this

=(RIGHT(A9,2)&LEFT(A9,4))

but it just spits out mmyyyy and not in a date format. When I try to format the new cell as a date it does nothing. Any help?

Upvotes: 2

Views: 540

Answers (1)

teylyn
teylyn

Reputation: 35915

Excel cannot represent dates before 1900. That's why some clever person has used the text format 1871.01 to represent January 1871. You can use this in charts to plot the date in a category (not a date) axis.

If you do want to use a time axis you may want to approach it like this:

  • extract the year part and add 100
  • extract the month part and build a date that is 100 years after the date shown
  • plot the data with the new date in a scatter chart (not a line chart)
  • in the worksheet, generate a range of dates for the date range of the chart and corresponding 0 values
  • create a helper cell for labels that show the 1800 equivalent date as text in the desired format
  • add the helper series to the chart
  • remove the chart x axis labels
  • add labels to the helper series with "Values from cells" and position "below"
  • format the helper series to have no line and no marker (still visible in the screenshot.

With this method you can place as many or as few fake X axis labels as you like, by creating or removing values in the helper for label cells in the grid.

It is a bit more setup than plotting dates after 1900 in a timeline, but it can totally be done.

enter image description here

Upvotes: 2

Related Questions