time recording to excel

Dear sir and friend,

Thank you for kind teaching and advice.
I have a question about time recording to Excel.

For summary:

  1. arduino uno or mega and RTC and SD card
  2. several analogue inputs
  3. time and analogue inputs recording to excel's cell
    example) 09/27/2016 1:33 25 35 19 23

What is the problem?
Excel's colum shows always 09/27/2016 1:33.
But, there is the code for second.

If I double click the cell for time, I can read 'second' in the cell as 09/27/2016 1:33.41 AM.
As if second number is hidden..
I want to read all time number include second at the same time.

This is the code.
Please, let me know the erros in the code.

I think that the problem is the code -dataFile.print(" , "); -.
How about you?

Thank you in advance.

void loop()
{
    DateTime now = RTC.now();

 
    String dataString = "";
    for (int analogPin = 0; analogPin < 3; analogPin++) {
    float sensor = (analogRead(analogPin)/1023.0*5.0);
   
    dataString += String(sensor);
  
  if (analogPin < 2) {
      dataString += "    ,    ";
    }
  }

  dataFile.print(now.year(), DEC);   
  dataFile.print('/');
  dataFile.print(now.month(), DEC); 
  dataFile.print('/');
  dataFile.print(now.day(), DEC); 
  dataFile.print(' '); 
  dataFile.print(now.hour(), DEC); 
  dataFile.print(':');
  dataFile.print(now.minute(), DEC); 
  dataFile.print(':');
  dataFile.print(now.second(), DEC); 
  dataFile.print(" , "); 
     
  dataFile.println(dataString);
  
  dataFile.flush();
   
  delay(200);
}

If I double click the cell for time, I can read 'second' in the cell as 09/27/2016 1:33.41 AM.

So, the problem is NOT with the Arduino code.

Is your column wide enough to show all the text in the column?

Thank you.

The excel column wide length is enough.
Although I drag the cell line to extend, the second number isn't showed.

Although I drag the cell line to extend, the second number isn't showed.

How is the cell formatted? If it is formatted to show the contents as a date and time string, that formatting probably does not include seconds.

Try formatting the column to display text.

Thank you so much Pauls'

As you mentioned, if the cell or column is changed to the time and date format, I can read 'second number'.

But, isn't there any method without changing cell format?

The fact: although I don't change the cell format, I can read all numbers except second.
It is likely to be a method.

Maybe opening an excel document based on a template with correct formatting of the colums can do the trick.

You might also be able to force excel to treat the date/time as text by putting a single tick in front of the date/time that you send; excel will hide the single tick (') if I'm not mistaken. But I hate corrupting data just for the sake of display purposes.

You can do it by preventing Excel recognising your timestamp as its own dateTime datatype by, for example, including an '_' character between the date part and the time part.

6v6gt:
You can do it by preventing Excel recognising your timestamp as its own dateTime datatype by, for example, including an '_' character between the date part and the time part.

Thank you so much. 6v6gt,

Success!. Thank you so much!

I got what I wanted thanks to you.
It goes well by using '_' character.

Thank you again and again.

sterretje:
Maybe opening an excel document based on a template with correct formatting of the colums can do the trick.

You might also be able to force excel to treat the date/time as text by putting a single tick in front of the date/time that you send; excel will hide the single tick (') if I'm not mistaken. But I hate corrupting data just for the sake of display purposes.

Thank you for kind advice, sterretje,
I'm a biginner, so it is difficult to make a nice and perfect code.

Please understand me,
Thank you again!

Beginner in what? Excel or arduino?

donald0000:
But, isn't there any method without changing cell format?

Why do you need another method? Excel is what's mangling the display of the data, so Excel is what you should be fixing.

To Mr. sterretje,

I'm a beginner for arduino programming.
Thank you so much.

To Mr. Jiggy-Ninja,
Thank you for your kind advice.
You're right.
I'm learning and learning, day by day.

donald0000:
To Mr. sterretje,

I'm a beginner for arduino programming.
Thank you so much.

Does that imply that you know Excel :wink:

Sometimes the (semi-)intelligent way that Excel behaves can drive people up the wall.

If you export to excel I suppose this is to do something more than displaying columns? Wouldn't it be actually better if excel was recognizing your dates correctly as dates so that you can do time graph properly for example ?

This is really an excel import challenge when reading your text file.

I would suggest to separate the day and the time into two columns rather than just one, this way excel recognizes the format properly for both and you get usable data in your cells