Why is my time string different on my Excel spreadsheet ... only using an executable

[mzlhb]

Hello.

My program works fine, but my customer requested that some of the timestamps be in the minute:second format. I've used the time format string %M:%S to save my data to an Excel file. On my computer, it works fine ... but when I created an executable and installed it onto the customer's computer the minute: second format had additional zeros following. For example: 46 minutes, 45 seconds is displayed as 46:45 on my computer ... but is displayed as 46:45:00 with the executable installed. Also, when I select the cell .. it shows 10:45:00 PM I've attached my code ... I know that it's slightly messy, but could you please focus on the time format strings only. The strings are in the main vi titled, "Temperature_Measurement_rev2.vi". I've also attached a good and bad file. Thanks

[Sam_Sharp]

At a guess, I would say that they have different default formatting options for times in their Excel installation. Perhaps it's related to the date/time/locale settings on the customer's computer?

[PatrickLye]

Have you looked at:

http://zone.ni.com/reference/en-XX/help/372120A-01/lvoffice/excel_set_cell_format/

You should be able to control the spreadsheet as you build it. Sorry, can't open your code right now my system is running upgrades.

[Bob_Schor]

When I open the GoodFile and BadFile Excel Workbooks you have attached, I'm not certain that I see a difference (in my Excel 2010) in the time displays.  I'm attaching screen shots of what I see -- both GoodFile (left) and BadFile (right) seem to have similar time formats.

Bob Schor

[mzlhb]

Bob_Schor,

My problem is down farther ... in the "Time (min:sec)" column. Also, when you click on one of the individual cells ... it's no longer in the minute:second format from LabVIEW.

[Gregory]

You can use the cell format vi. Although it looks like you might want to grab the hour from the timestamp or pad it with a zero (even though you don't want to display it). It seems like Excel expects times to have hours.

[Bob_Schor]

Thanks, I was looking in the wrong place.

You have two (different) problems in the Column A Time entries in BadFiles that are causing you problems.  You can compare the cells in GoodFiles with those in BadFiles to see this.

First, GoodFiles are formatted with the hh:mm format, which does not show seconds, while BadFiles uses h:mm:ss.  Not sure why this happens, but that will give you "seconds".

Second, if you click on cell A15 in GoodFiles, you will see 12:40:00 AM, or 0 hours 40 minutes after midnight.  I haven't looked at your LabVIEW code to see what you are actually writing, but time variables are funny, and represented differently in Excel and LabVIEW.  Usually the simpler way to send them between these programs is as text, which is what I assume you did.

Now look at BadFiles.  The entry you probably expect to say 40 minutes instead says 1/1/1900 10:40:00 pm, or 40 minutes after midnight in the UTC-2 Time Zone (I think I did that right, but I get confused with the signs).  Again, not sure where things are going awry, but it definitely involves the LabVIEW versus Excel method of handling times.

You do seem to be sending the data as numeric (TimeStamp) quantities -- have you considered sending it as a string?  If you click on an A-Column cell in BadFile and enter "0:46" to replace "1/1/1900 10:46:00 PM", it will display 0:46:00 (because I hadn't changed the cell format) and the Cell contents will show 12:46:00 AM, or 46 minutes past midnight, consistent with the contents of GoodFiles (minus the cell format issues).

Bob Schor

[Gregory]

Building on Bob's response, but referring to my block diagram:

You could use the "%M:%S" formatted timestamp that you currently use in LabVIEW, and set the Excel display string to "hh:mm". You would be "lying" in that you are actually displaying minutes and seconds, not hours and minutes, but it would format it correctly. Though if your customer highlights the cell it would show "mins:secs:00" which might confuse them.

[JÞB]

Whooo..

That would be your problem!  the implied %<>c on the format specifier for the relative timestamp constant uses the local machine time. a %<>T might help or use an absolute time format