How can i plot time in the x-axis of a scatter plot in excel?

0
votes

I have some data from a 24 hour measurement of a sensor, see the attached image. I want to plot it on a scatter graph in excel. The row with the time data should be plotted on the x-axis. The row with the other data should be plotted on the y-axis.

I have tried to create a scatter plot and add the row with the date and time to the x-axis and the row with the data to the y-axis. However excel just plot 12:00 AM 7 times randomly.

What i want: I want to plot the date and time on the x-axis. Furthermore, i would like to have the time and data printed every 2-hours.

Could someone tell me how to do this?

Screenshot of excel document

2
excel
your date and times may actually be text and not a numerical date and time number. I am only guessing this as your date times seem to be left justified. You can test this out in a couple of ways. one is to change the cell formatting to general and see if the displayed date time changes. If it does you have it in a number format which is what you want. The other options are =ISTEXT(cell) or =ISNUMBER(cell). If you date is actually text, you will want to convert if to a number first before plotting. – Forward Ed
It is indeed text, the date DATEVALUE() function doesn't succeed in converting it. Do you know another method? – Mauricio Paulusma
See one of the answers below that uses text to column to translate date and time as text to an actual number. If for some reason that fails for you, you can use a bunch of text manipulation formulas to pull out the individual numbers and then drop them into appropriate of DATE and TIME formula. Some formulas you may want to consider are: LEFT, RIGHT, MID, TIMEVALUE and DATE. – Forward Ed
Apparently I answered a similar question on how to convert in the previous question: stackoverflow.com/questions/46414111/… – Forward Ed

2 Answers

0
votes

When entering proper timestamps in Excel, the scatterplot comes out right immediately (even though the axis labels need some tailoring to taste). Timestamps

I reproduced your problem by intentionally entering text instead of time data in the first column. As noted by @ForwardEd, the error stands out because the text data are left-justified. I had to seriously tweak a CSV import to get this wrong on purpose - Excel seems to be getting frighteningly smart at recognizing import data.

I got exactly the same 7 labels "12:00:00 AM" like you had, but the ultimate indication of what is going on, is to put the axis labels in date format. Based on no input, Excel has made up a series of consecutive dates starting with the awesome Zeroeth of January, 1900 ! TextData

So your actual problem is getting the proper timestamps imported from some logfile. Note how the Text Import Wizard offers the feature to customize each of the imported columns for data type. "General" is the default, which does a fine job of recognizing dates, but you may need to nudge the process in the right direction by explicitly choosing "Date" type. That option allows you to further specify the DMY order.

I'm guessing that your logfile is written by a device with a 'locale' that differs from the Internationalization settings on your machine, which makes it harder for Excel to properly recognize the timestamps. As a last resort, you could reshape the timestamps in your logfile to some format that Excel can handle. Import

0
votes

Excel does not plot the time randomly. You can clearly see on the X axis that the markers are for 0:00 AM and these are consecutive days.

Format the X axis and you can see the settings for the Axis Options > Units will be 1 (for 1 day). It looks like the format for the axis labels is using only the time without the date.

(By the way: The row with the time is not a row, but a column. Rows go left to right, columns go top to bottom)

If you want the time and date printed every two hours, you will need to

  • format the labels as date and time
  • make the major interval on the horizontal axis 1/12 of a day.

A day is 1 in Excel, so the major interval needs to be 1 divided by 12, which is 0.08333333.

That will cause all the date/time stamps to overlap and become unreadable, so you may want to revise that decision or make your chart very, very wide if you have more than a few hours of data or turn the X axis labels sideways which will look like this:

enter image description here

Not pretty, but that's what you asked for. If you change your mind, edit your question and post a comment to let me know what you want.

Edit: Looks like your "dates" may actually be text. Format the X axis labels to "General" and you may find that the labels are integer numbers. Excel cannot recognize the text in column A as dates, so it just numbers the data points from first to last and plots these numbers on the X axis. Formatted as times, all integers will show up as midnight.

You may want to convert your text to real date/times with the Text to Columns tool or another technique of your choice.

Once the data in the cells is dates with times, you can apply what I have written above.