Charting Temperature vs Data and Time

**kavanaa1** · 01-18-2013, 12:07 PM

18-01-2013 16-06-08.jpgHi there

How do I plot temperature values against data/time values from a single cell? The attached picture shows what I'm getting currently

Date Temp

Column A Column B
1/18/2013 3:28:03 PM 20.4
1/18/2013 3:13:03 PM 20.7
1/18/2013 2:58:02 PM 20.3
1/18/2013 2:43:01 PM 20.7
1/18/2013 2:27:59 PM 20.4
1/18/2013 2:12:58 PM 20.5
1/18/2013 1:57:57 PM 20.7
1/18/2013 1:42:56 PM 20.4
1/18/2013 1:27:55 PM 20.5
1/18/2013 1:12:53 PM 20.8
1/18/2013 12:57:51 PM 20.4

18-01-2013 16-06-08.jpg

**MrShorty** · 01-18-2013, 12:29 PM

It is hard to tell from your picture (a workbook might be easier to debug from). It appears that the chart in your picture is using 1,2,3,4,5,... for the "x values" (which formats to 1/1/1900 when formatted as dates) in what looks to be a scatter plot. This is what Excel does if the "x values" data is text rather than something numeric. If I had to guess, I would suspect that Excel is not interpreting your dates as dates, but is seeing them as text. Check your date time column to see if it is text or date.

**kavanaa1** · 01-18-2013, 12:58 PM

Appreciate response please see attached workbook. have plotted a new xy scatter. Does seem to be excel is not recognising the data/time. Imported from csv file.

Would like to be able to sort oldest to newest also but need excel to recognise its a date/time entry first and in the right format.

**lmp** · 01-19-2013, 04:34 AM

Hello,

when you import the CSV file, you need to specify that the date is in MDY format. I assume your system (as well as mine) uses DMY by default, so a date like 1/18/2013 is not valid as DMY.

I have not succeeded in importing the data as date/time values, though. My suggestion for converting the text to real date/time values:

Insert three new columns between the date and the temperature.
Select column A and perform Data > Text to Columns > Delimited with the Space character.
In Step 3 of the Text to Columns Wizard, select the first column and set the Column data format to Date with MDY chosen from the dropdown. Then finish the wizard.

Now you will have columns A, B and C with a (valid) date, a time and the AM/PM marker. In column D use this formula to put it all back together:

=A2+TIMEVALUE(TEXT(B2,"h:mm:ss")&" "&C2)

Copy column D, use Paste Special > Value to paste into column D, delete columns A to C and then build your chart.