How to get a time value into my spreadsheet

[vladosaurus]

Hi everyone,
i trying to export the readings of my program into spreadsheet but I have a problem with a Time Value. The problem finds place in the "Release data" state.

Time Value is exported as a decimal number and not as a time format, as I want it to be. Check my files. 

If you want to test the program to see what I am talking about you can do it only with a - Wait -  function:

1. Set Wait in the 1st row and the Time criterion.

2. Check the Export box.

3. Press START and the program is going to ask you where to save the data.

4. Wait the program to finish the counting or press the STOP button for a few sec.

5. Open the Unknown data file with your Excel, or any spreadsheet program.

Does anyone has an Idea how to do it? I just cant get it.

Thanks in advance!

Vladimir B.

[GerdW]

Hi vlado,

but I have a problem with a Time Value. The problem finds place in the "Release data" state.

Yes, your description is true: there is a problem in this case of your case structure!

Did you also notice the big red coercion dot at the WriteSpreadsheet function?

When you want to save waveforms you should use the WriteWaveforms function!

(And why do you need to create several waveforms containing just one sample?)

Suggestion:

[vladosaurus]

Hi GerdW,

thanks for your advice.

I made it as you told me, but I still don't get a proper values in the spreadsheet. I want it to be saved automatically into time format hh:mm:ss and not as a decimal number.

(And why do you need to create several waveforms containing just one sample?)

Well, I think it is practical for a logging file. Do you have any advice how otherwise i can do it?

Thanks !

Best Regards

Vlado

[GerdW]

Hi vlado,

I made it as you told me, but I still don't get a proper values in the spreadsheet.

Please define "proper" values! I think you get correct values…

I want it to be saved automatically into time format hh:mm:ss and not as a decimal number.

Then you cannot save your data as CSV files (aka spreadsheet data)!

When you want to use certain Excel features like cell formatting you need to save your data as real Excel files - either using RGT (ReportGenerationToolkit), the underlying ActiveX calls or third-party toolkits available in VIPM…

Btw. the "XLS" files you attach with your messages are just plain text files. Please name them either "TXT" or "CSV"…

Well, I think it is practical for a logging file.

How can this be "practical" when you ignore the coercion dot in your VI?

[arteitle]

---

@vladosaurus wrote:

I made it as you told me, but I still don't get a proper values in the spreadsheet. I want it to be saved automatically into time format hh:mm:ss and not as a decimal number.

---

You can save times in the format 

HH:MM:SS

in a CSV file, just not using Write Delimited Spreadsheet.vi, since it can only use the one format specifier for all cells (in this case the default, %.3f). Instead you can use something like Format Into File, specifying the desired format for the time using Time Format String codes (e.g. %<%H:%M:%S>T ). Excel will correctly interpret times in the format

HH:MM:SS

.

One problem I've run into is that if you include fractional seconds, e.g.

HH:MM:SS.ff

, since Excel doesn't have a predefined display format for this, it'll hide the hours and just show you

MM:SS.ff

, and if you then resave the file in Excel format, it'll forget the hours completely. The fix is, as soon as you open the file in Excel, to immediately create a custom display format for your time column that includes hours, minutes, seconds, and fractional seconds.

edit: Dumb auto-emoji feature... If you have it turned on, which I think it is by default, then everywhere above that I wrote a colon followed by a capital S will be replaced by a scowling emoji face. It's a terrible feature that I think I can only override by putting the text in a Code block. I'll try that...

[RavensFan]

Please don't put stuff in a code box.

To fix the problems with emoticons:

Go to your Discussion Forum profile settings, My settings, Preferences, Display.

There is an option called "Emoticon Type" it is probably set on Use default (smiley).  Change it to None.

[arteitle]

Correct me if I'm wrong, but I thought the Emoticon Type setting only affected how messages were displayed, now how they were composed. In other words, if I changed that setting it would prevent me from seeing Emoticons, but it wouldn't prevent anyone else from seeing them in messages I had composed, even if I didn't want them there. I thought using a Code block (or maybe some other HTML) was the only way I could prevent them from being displayed for everyone else.

edit: I'm inclined to leave emoticon substitution enabled on my end, as much as I detest it, because it's the default and most users will have it enabled. That way I can see my postings as most others will, and can tell when dreaded emoticons have been substituted into my text against my wishes.

[RTSLVU]

---

@vladosaurus wrote:

I want it to be saved automatically into time format hh:mm:ss and not as a decimal number.

You do know in Excel you can just highlight the column, right click, and select "Format Cells", then select the time format you want, right?

BTW: If this does not work then the "numbers" you are saving are NOT a proper timestamp.

[arteitle]

A LabVIEW timestamp is the number of seconds since 01/01/1904 00:00:00.000 UTC, but an Excel "serial date" is the number of days since 1/1/1900, so you can't just save a LabVIEW timestamp and have Excel understand it without doing some math. If you're writing a CSV file, it makes sense to put the date in a human-readable format that Excel can understand, like hours:minutes:seconds.

[RavensFan]

Yes.  It only changes the way it looks for you.  Unfortunately NI set a bad default for that setting.  I think automatic emoticons should be eliminated entirely.  If someone wants to put in an emoticon, then it isn't hard to put one in.

But having one automatically put in when it shouldn't, and worse have that be the default setting unless you do the steps I listed, is just a bad idea.

The goal should be to get NI to turn off that setting.  Until then, everyone who wants to see the text correctly should set their own settings to display it.  Forcing text to be in code block is a hack.