Cannot create chart time and date against humidity or temperature

[mateuszriou123]

Hello all,

First time posting here so I hope it is okay.

I have been scratching my head for a while about plotting graphs time and date against humidity or temperature.

I have my data and I try to highlight a column with time and the other with humidity for example and I get this weird data plot. Here is a photo.

Graph 1.PNG

I also attach the data as an example.

"Date " "Time " "Humidity in % " "Temp_DHT in °C " "Temp_1 in °C " Temp_2 in °C
27/6/2019 9:19:2 " 68.00% " "25.10 " "26.20 " "24.18 "
27/6/2019 9:19:4 " 67.00% " "25.00 " "26.20 " "24.18 "
27/6/2019 9:19:6 " 64.00% " "25.00 " "26.20 " "24.18 "
27/6/2019 9:19:8 " 65.00% " "25.00 " "26.20 " "24.18 "
27/6/2019 9:19:10 " 65.00% " "25.10 " "26.20 " "24.18 "
27/6/2019 9:19:12 " 65.00% " "25.10 " "26.20 " "24.18 "
27/6/2019 9:19:14 " 65.00% " "25.10 " "26.20 " "24.18 "
27/6/2019 9:19:16 " 66.00% " "25.10 " "26.20 " "24.18 "
27/6/2019 9:19:18 " 66.00% " "25.10 " "26.20 " "24.18 "
27/6/2019 9:19:20 " 66.00% " "25.10 " "26.20 " "24.18 "
27/6/2019 9:19:22 " 66.00% " "25.20 " "26.20 " "24.18 "
27/6/2019 9:19:24 " 66.00% " "25.20 " "26.20 " "24.18 "
27/6/2019 9:19:26 " 65.00% " "25.20 " "26.20 " "24.18 "
27/6/2019 9:19:28 " 65.00% " "25.20 " "26.20 " "24.18 "
27/6/2019 9:19:30 " 65.00% " "25.20 " "26.20 " "24.18 "
27/6/2019 9:19:32 " 65.00% " "25.30 " "26.69 " "24.18 "
27/6/2019 9:19:34 " 65.00% " "25.20 " "26.20 " "24.18 "
27/6/2019 9:19:36 " 65.00% " "25.20 " "26.20 " "24.18 "
27/6/2019 9:19:38 " 65.00% " "25.20 " "26.20 " "24.18 "
27/6/2019 9:19:40 " 64.00% " "25.20 " "26.20 " "24.18 "
27/6/2019 9:19:42 " 64.00% " "25.20 " "26.20 " "24.18 "
27/6/2019 9:19:44 " 64.00% " "25.20 " "26.20 " "24.18 "
27/6/2019 9:19:46 " 64.00% " "25.30 " "26.20 " "24.18 "
27/6/2019 9:19:48 " 64.00% " "25.30 " "26.20 " "24.18 "
27/6/2019 9:19:50 " 64.00% " "25.30 " "26.20 " "24.18 "

[Kaper]

probably you have added headers column1, column2 etc, whlie youir original heades are treated as regular data.

If you go a step back, and prepare tyour table with your real headers, the graphs shall be bread and butter easy.

As for charting "date and time" it would be wise to have full time description in one column like "date and time" column and it shall be easily obtainable just by adding date and time (provided there are real dates and times in the cells, not jutst texts, which only look like a date or time)

See attached sample

[mateuszriou123]

Thank you, I will see what I can do.

Nice you are Polish? Czesc

[mateuszriou123]

Hello,

So I have tried to use your version and I tried to create a chart with time against humidity and I am still having the very same problem.

Could this be because the formatting is incorrect or is it excel not being able to interpret the data?

Thank,
Mateusz

[MrShorty]

I am not certain exactly what difficulties you are having. Can you upload an Excel file rather than a picture?

I have occasionally seen the scenario in your picture, where Excel completely fails to parse the desired data ranges for the chart when the chart is created. Normally this should be easy to fix by bringing up the Select Data dialog and fixing Excel's mistake. https://www.dummies.com/software/mic...in-excel-2013/ From the point shown in your picture, I would have expected to open the select data dialog and edit the data ranges so that the horizontal category X axis data is being taken from B2:B27 and the values for the data series are taken from C2:C27.

Does that help?

[Kaper]

Cześć,

Sure, formatting could be wrong, (as a matter of fact - data type not the formatting itself) also you may use wrong graph type or select wrong ranges before inserting a graph.

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. (for this case draw sample graph on paper and attach a picture of it, but picture reflecting the data from the spreadsheet) Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[mateuszriou123]

I have just uploaded my recent readings.

This is how they look like on notepad form. These readings are saved on SD card from Arduino.

Date ,Time ,Humidity in % ,Temp_DHT in °C ,Temp_1 in °C ,Temp_2 in °C
28/6/2019, 13:53:27, 50.00% ,28.30 ,37.86 ,30.04
28/6/2019, 13:54:27, 49.00% ,28.30 ,38.84 ,30.04
28/6/2019, 13:55:27, 49.00% ,28.40 ,40.31 ,30.04
28/6/2019, 13:56:27, 49.00% ,28.40 ,40.31 ,30.04
28/6/2019, 13:57:27, 49.00% ,28.40 ,39.82 ,30.04
28/6/2019, 13:58:27, 48.00% ,28.50 ,39.82 ,30.04
28/6/2019, 13:59:27, 49.00% ,28.50 ,39.82 ,30.04
28/6/2019, 14:0:27, 48.00% ,28.50 ,39.82 ,30.04
28/6/2019, 14:1:27, 48.00% ,28.50 ,39.82 ,30.04
28/6/2019, 14:2:27, 48.00% ,28.50 ,39.82 ,30.04
28/6/2019, 14:3:27, 48.00% ,28.50 ,39.82 ,30.04
28/6/2019, 14:4:28, 48.00% ,28.50 ,39.82 ,30.04
28/6/2019, 14:5:28, 48.00% ,28.50 ,39.82 ,30.04
28/6/2019, 14:6:28, 48.00% ,28.50 ,39.82 ,30.04
28/6/2019, 14:7:28, 48.00% ,28.50 ,39.82 ,30.04
28/6/2019, 14:8:28, 48.00% ,28.50 ,40.31 ,30.53
28/6/2019, 14:9:28, 48.00% ,28.60 ,39.82 ,30.04
28/6/2019, 14:10:28, 48.00% ,28.60 ,40.31 ,30.53
28/6/2019, 14:11:28, 48.00% ,28.60 ,39.82 ,30.04
28/6/2019, 14:12:28, 48.00% ,28.60 ,39.82 ,30.04
28/6/2019, 14:13:28, 48.00% ,28.60 ,39.82 ,30.04
28/6/2019, 14:14:28, 48.00% ,28.60 ,39.82 ,30.04
28/6/2019, 14:15:28, 48.00% ,28.60 ,39.82 ,30.04
28/6/2019, 14:16:29, 48.00% ,28.60 ,39.82 ,30.04
28/6/2019, 14:17:29, 48.00% ,28.60 ,40.31 ,30.04
28/6/2019, 14:18:29, 48.00% ,28.60 ,39.82 ,30.04
28/6/2019, 14:19:29, 48.00% ,28.60 ,39.82 ,30.04
28/6/2019, 14:20:29, 48.00% ,28.60 ,39.82 ,30.04
28/6/2019, 14:21:29, 48.00% ,28.60 ,40.31 ,30.53
28/6/2019, 14:22:29, 48.00% ,28.60 ,40.31 ,30.53
28/6/2019, 14:23:29, 48.00% ,28.60 ,40.31 ,30.04
28/6/2019, 14:24:29, 48.00% ,28.70 ,40.80 ,30.53
28/6/2019, 14:25:29, 47.00% ,28.70 ,39.82 ,30.53
28/6/2019, 14:26:29, 48.00% ,28.70 ,39.82 ,30.04
28/6/2019, 14:27:30, 47.00% ,28.70 ,40.31 ,30.53
28/6/2019, 14:28:30, 47.00% ,28.70 ,40.31 ,30.53
28/6/2019, 14:29:30, 47.00% ,28.70 ,39.82 ,30.53
28/6/2019, 14:30:30, 47.00% ,28.70 ,39.82 ,30.53
28/6/2019, 14:31:30, 47.00% ,28.70 ,38.84 ,30.53
28/6/2019, 14:32:30, 47.00% ,28.60 ,38.35 ,30.53
28/6/2019, 14:33:30, 47.00% ,28.60 ,37.86 ,30.53
28/6/2019, 14:34:30, 47.00% ,28.50 ,37.38 ,30.53
28/6/2019, 14:35:30, 47.00% ,28.50 ,36.89 ,30.04
28/6/2019, 14:36:30, 47.00% ,28.50 ,36.40 ,30.04
28/6/2019, 14:37:30, 47.00% ,28.40 ,35.91 ,30.04
28/6/2019, 14:38:30, 47.00% ,28.40 ,35.42 ,30.04
28/6/2019, 14:39:31, 48.00% ,28.30 ,35.42 ,30.04
28/6/2019, 14:40:31, 48.00% ,28.20 ,34.44 ,30.04
28/6/2019, 14:41:31, 48.00% ,28.20 ,34.93 ,30.04
28/6/2019, 14:42:31, 48.00% ,28.20 ,34.44 ,30.04
28/6/2019, 14:43:31, 48.00% ,28.10 ,34.44 ,29.56
28/6/2019, 14:44:31, 48.00% ,28.10 ,33.95 ,29.56
28/6/2019, 14:45:31, 48.00% ,28.10 ,33.95 ,29.56
28/6/2019, 14:46:31, 49.00% ,28.00 ,33.47 ,29.56
28/6/2019, 14:47:31, 49.00% ,28.00 ,33.47 ,29.56
28/6/2019, 14:48:31, 49.00% ,28.00 ,32.98 ,29.56
28/6/2019, 14:49:31, 49.00% ,28.00 ,32.98 ,29.56
28/6/2019, 14:50:32, 48.00% ,27.90 ,32.98 ,29.56
28/6/2019, 14:51:32, 48.00% ,27.90 ,32.49 ,29.56
28/6/2019, 14:52:32, 48.00% ,27.90 ,32.49 ,29.56
28/6/2019, 14:53:32, 48.00% ,27.90 ,32.49 ,29.07
28/6/2019, 14:54:32, 48.00% ,27.90 ,32.49 ,29.07
28/6/2019, 14:55:32, 49.00% ,27.90 ,33.47 ,29.56
28/6/2019, 14:56:32, 49.00% ,27.90 ,34.93 ,29.56
28/6/2019, 14:57:32, 48.00% ,27.90 ,36.40 ,29.56
28/6/2019, 14:58:32, 49.00% ,28.00 ,37.38 ,29.56
28/6/2019, 14:59:32, 49.00% ,28.00 ,38.35 ,29.56
28/6/2019, 15:0:32, 48.00% ,28.10 ,39.33 ,30.04
28/6/2019, 15:1:32, 48.00% ,28.10 ,40.31 ,30.04
28/6/2019, 15:2:33, 48.00% ,28.20 ,40.31 ,30.04
28/6/2019, 15:3:33, 48.00% ,28.30 ,40.80 ,30.04
28/6/2019, 15:4:33, 47.00% ,28.40 ,40.31 ,30.53
28/6/2019, 15:5:33, 47.00% ,28.40 ,39.82 ,30.04
28/6/2019, 15:6:33, 47.00% ,28.40 ,39.82 ,30.04
28/6/2019, 15:7:33, 47.00% ,28.50 ,39.82 ,30.04
28/6/2019, 15:8:33, 47.00% ,28.50 ,40.31 ,30.04
28/6/2019, 15:9:33, 47.00% ,28.50 ,40.31 ,30.04
28/6/2019, 15:10:33, 47.00% ,28.60 ,39.82 ,30.04
28/6/2019, 15:11:33, 47.00% ,28.60 ,40.80 ,30.53
28/6/2019, 15:12:33, 47.00% ,28.60 ,40.31 ,30.04
28/6/2019, 15:13:34, 47.00% ,28.60 ,40.31 ,30.04
28/6/2019, 15:14:34, 47.00% ,28.60 ,40.31 ,30.04
28/6/2019, 15:15:34, 46.00% ,28.60 ,40.31 ,30.04
28/6/2019, 15:16:34, 46.00% ,28.70 ,39.82 ,30.04
28/6/2019, 15:17:34, 46.00% ,28.70 ,39.82 ,30.04
28/6/2019, 15:18:34, 46.00% ,28.70 ,40.31 ,30.53
28/6/2019, 15:19:34, 46.00% ,28.70 ,40.31 ,30.04
28/6/2019, 15:20:34, 46.00% ,28.80 ,40.31 ,30.53
28/6/2019, 15:21:34, 46.00% ,28.80 ,40.31 ,30.53
28/6/2019, 15:22:34, 46.00% ,28.80 ,39.82 ,30.53
28/6/2019, 15:23:34, 46.00% ,28.80 ,40.31 ,30.53
28/6/2019, 15:24:34, 46.00% ,28.80 ,40.31 ,30.53
28/6/2019, 15:25:35, 46.00% ,28.80 ,40.31 ,30.53
28/6/2019, 15:26:35, 46.00% ,28.80 ,39.82 ,30.53
28/6/2019, 15:27:35, 46.00% ,28.80 ,39.82 ,30.53
28/6/2019, 15:28:35, 46.00% ,28.80 ,40.31 ,30.53
28/6/2019, 15:29:35, 46.00% ,28.80 ,40.31 ,30.53
28/6/2019, 15:30:35, 46.00% ,28.80 ,40.31 ,30.53
28/6/2019, 15:31:35, 46.00% ,28.80 ,40.80 ,30.53
28/6/2019, 15:32:35, 46.00% ,28.90 ,39.82 ,30.53
28/6/2019, 15:33:35, 46.00% ,28.90 ,39.82 ,30.53
28/6/2019, 15:34:35, 46.00% ,28.90 ,40.31 ,30.53
28/6/2019, 15:35:35, 46.00% ,28.90 ,40.31 ,30.53
28/6/2019, 15:36:36, 46.00% ,28.90 ,39.82 ,30.53
28/6/2019, 15:37:36, 46.00% ,28.90 ,39.82 ,30.53
28/6/2019, 15:38:36, 46.00% ,28.90 ,39.82 ,30.53
28/6/2019, 15:39:36, 46.00% ,28.90 ,39.82 ,30.53
28/6/2019, 15:40:36, 46.00% ,28.90 ,39.82 ,30.53
28/6/2019, 15:41:36, 46.00% ,28.90 ,39.82 ,30.53
28/6/2019, 15:42:36, 46.00% ,28.90 ,40.31 ,30.53
28/6/2019, 15:43:36, 46.00% ,28.90 ,40.31 ,30.53
28/6/2019, 15:44:36, 46.00% ,28.90 ,40.31 ,30.53
28/6/2019, 15:45:36, 46.00% ,28.90 ,39.82 ,30.53
28/6/2019, 15:46:36, 46.00% ,28.90 ,39.82 ,30.53
28/6/2019, 15:47:37, 46.00% ,28.90 ,40.31 ,30.53
28/6/2019, 15:48:37, 46.00% ,28.90 ,39.82 ,30.53
28/6/2019, 15:49:37, 46.00% ,28.90 ,40.31 ,30.53
28/6/2019, 15:50:37, 47.00% ,29.00 ,39.82 ,30.53
28/6/2019, 15:51:37, 47.00% ,29.00 ,39.82 ,30.53
28/6/2019, 15:52:37, 47.00% ,29.00 ,40.31 ,30.53
28/6/2019, 15:53:37, 47.00% ,29.00 ,39.82 ,30.53
28/6/2019, 15:54:37, 47.00% ,29.00 ,40.31 ,30.53
28/6/2019, 15:55:37, 46.00% ,28.90 ,39.82 ,30.53
28/6/2019, 15:56:37, 46.00% ,28.90 ,40.80 ,31.02
28/6/2019, 15:57:37, 47.00% ,29.00 ,39.82 ,30.53
28/6/2019, 15:58:37, 47.00% ,29.00 ,40.31 ,30.53
28/6/2019, 15:59:38, 47.00% ,29.00 ,40.31 ,30.53
28/6/2019, 16:0:38, 47.00% ,29.10 ,39.82 ,30.53
28/6/2019, 16:1:38, 47.00% ,29.00 ,39.82 ,30.53
28/6/2019, 16:2:38, 47.00% ,29.00 ,40.31 ,30.53
28/6/2019, 16:3:38, 47.00% ,29.10 ,40.31 ,30.53
28/6/2019, 16:4:38, 47.00% ,29.00 ,40.31 ,30.53
28/6/2019, 16:5:38, 47.00% ,29.10 ,40.31 ,30.53
28/6/2019, 16:6:38, 47.00% ,29.10 ,40.31 ,30.53
28/6/2019, 16:7:38, 47.00% ,29.10 ,40.31 ,30.53
28/6/2019, 16:8:38, 47.00% ,29.10 ,40.31 ,31.02
28/6/2019, 16:9:38, 47.00% ,29.10 ,40.31 ,30.53
28/6/2019, 16:10:39, 47.00% ,29.10 ,40.31 ,31.02
28/6/2019, 16:11:39, 47.00% ,29.10 ,40.31 ,30.53
28/6/2019, 16:12:39, 47.00% ,29.10 ,40.31 ,31.02

[mateuszriou123]

I try to go to advanced options to upload the excel document but nothing appears it is just a blank white box.

[MrShorty]

I am not sure where you are going to for the "advanced options" but that doesn't sound like the right place to find the file uploader. Beneath the quick reply field on the right side is the "go advanced" button that will bring up the full featured post editor. Beneath the editing field is a link to "manage attachments" which should bring up the file uploader.

If I were creating a scatter chart to show temperature or humidity against time based on that CSV file, I would expect to:

1) Import/copy/open the text file into Excel. If Excel does not do it automatically, perform a Text to Columns command with comma as the delimiter. Check all of the fields that Excel correctly imported them (especially that it recognized date and time fields and correctly read them as date/time serial numbers).
2) If desired, add a column to combine date and time into a single date+time serial number =A2+B2
3) Select the desired Xvalue and Yvalues columns and insert a scatter chart.
3a) If Excel fails to correctly parse the desired Xvalues and Yvalues ranges (as suggested by the screenshot in the OP), bring up the Select Data dialog and fix those errors.
4) Other formatting as desired.

From what I see in the OP, you are stuck on step 3a.

[mateuszriou123]

Here is the spreadsheet

I have found it through your instruction.

[mateuszriou123]

In the meantime I will try myself to see if I can make it work.

Thank you for your continuous support.

[MrShorty]

It appears that all of your fields are being imported with extra spaces in each field, so Excel is seeing each field as simple text -- no numbers. This can be quickly tested using the ISTEXT() function.

The first step, therefore, to getting a good chart from this data is to remove those spaces from each field so that each field is a true number. Since these OLAP connections are to something I don't have access to (and I am not familiar with these OLAP imports), I am not sure what to recommend. I would carefully go through the import step(s) and adapt the import so that it will not include these extra spaces so that Excel can recognize each field as a number.

If you cannot find a way to clean up the text import at the import step, you can add helper columns with a formula like =VALUE(CLEAN(A3)) that will remove the spaces and convert to numbers in the spreadsheet. Then use these helper columns to create the chart.

[mateuszriou123]

Thank you for your feedback and yes I have come to realise that there are quite a few spaces making it just as you said text instead of numbers.

So would I just need to try and remove all the spaces?

[MrShorty]

Yes, you need to remove the spaces. The main question is whether to remove the spaces before or during import (need to look at the available import settings and controls) or remove the spaces after import using something like the CLEAN() function. I would be inclined to try to remove the spaces before/during import, but that depends on the options and settings available to the type of import you are doing.

[mateuszriou123]

Okay thank you Mr Shorty. I will try all that I can when it comes to removing spaces since I presume spaces counts as text.