Time Stamp incorrectly displayed in XLS file when Milliseconds is used

[Mitch_Peplow]

Hi all,

Got a bit of issue when trying to write the time to an XLS file when using the format HH:MM:SS.MS and it appears to be Excel causing the issues due to how it is interpreting and thus formatting the cells for that data.

I've attached a Vi that demonstrates the issue but i'll explain for clarity.

If you look at the BD of the VI you will see two format date/time string functions with a string format that gives me two variants of HH:MM:SS.MS and this can be seen when you run the VI in the File String Array indicator. The first cell gives the time as desired, however the second has had a deliberate extra : placed before the millisecond value for very good reason. 

Now, if you open the XLS file and expand the columns, you will see that the first cell is formatted completely wrong even though the array data was correct whereas the second cell is displayed exactly as it was in the array data.

If I look at the formatting of the first column, Excel recognises it as a time, however it is automatically formatting the cell incorrectly in the format of mm:ss.0 which basically means it chops off the hours and gives the millisecond value to one decimal point. If I custom format the Excel file with the format hh:mm:ss.000 I get the correct time displayed perfectly thus the data is correct coming into Excel, its just how Excel is formatting the cell. The second cell is display exactly as per the array data because Excel does not recognise it as a time format due to the extra : thus it writes it as a standard string. This is ok as a workaround but visually unappealing and more hassle if reading the data back as I would need to parse out the correct data.

Is there a simple solution to this that I'm overlooking or a way to programatically tell Excel not to do this?

Cheers in advance

Mitch

[chembo]

Here is some info about formatting Excel cells to display milliseconds: Formatting Excel sheet for displaying Timestamp with milliseconds.

The Write To Spreadsheet File.vi does not save the file in Excel format. It is just a plain text, tab delimited file.

Probably it will work if you save the timestamps without am/pm, i.e. 12:14:33.065, then read the file in Excel and change the formatting of the cells to [h]:mm:ss.000

I am not familiar with the Report Generation Toolkit in LabVIEW, but this can be done for sure via ActiveX automation. You can write the data directly to an Excel Spreadsheet, change the format and save the file in native Excel format.

[Mitch_Peplow]

Hi chembo,

Cheers for the quick reply, much appreciated.

Just realised the slight format issue when posting, i.e. the confused smiley face! Clearly that was meant to be seconds

I'm not sure if I've misunderstood your answer but currently I'm not writing the time with am/pm anyway, its purely HH:MM:SS.MS and the data is there in Excel, it's just formatted the cell in an alternative way to what I would like.

The link you sent is exactly what I've already tried which works but I want this automating or stopping altogether. I'll have a dig around with the ActiveX stuff as I've had to use that before to close all instances of Excel when performing FileIO operations.

Cheers again

Mitch

[Stuart.Parkinson]

So although this doesn't address your problem directly, it may be an indirect solution and possibly better in some circumstances.

Instead of formatting a timestamp as a string, take a timestamp and convert to a double, then use

excel = (timestamp / 86400) + 1462

and write that value as a double to the spreadsheet. This is now in Excel time format, and if you tell Excel the column contains a time then it will just display the time in the format you choose (I usually choose custom format hh:mm:ss.000)

[aputman]

I can't open your VI but if you are using the report generation toolkit, you can format a group of cells to any display format that you want.  I don't like to use the export to spreadsheet file because of this reason..  It is just a delimited txt file that Excel will interpret however it likes when opened.  In the Excel Specific menu of the RGT, there is an Easy Table function that will let you write to an actual excel file.  You select the insert point, fonts, format, etc. 

[RTSLVU]

Take my advice and change the way you are doing this now before it comes back to bite you in the rear later on down the road.

You are writing a time stamp as a formatted string to excel. While this works MOST of the time try playing around with the format cells function to change the time format in Excel. As if you were only given the data file and was doing some analysis on it. For instance what if you want the full DD/MM/YY HH:MM:SS:sss (that's not supposed to be an emoticon)

A time stamp should be written to Excel as a double precision floating point number, not preformatted text the VI below shows the conversion.

Time Stamp In is for synchronizing timestamps across multiple files leave it blank to use internal get date/time in seconds.

Adjust it for your time zone

I have a couple pre-fab VI's besides this one I use for writing numerical array data to Excel and adding the timestamp that have this rolled in I can upload if you would like them tell me what version you need them in.

Here is a example of what I use all the time:

[chembo]

---

@Mitch_Peplow wrote:

Hi chembo,

 ..........

I'm not sure if I've misunderstood your answer but currently I'm not writing the time with am/pm anyway, its purely HH:MM:SS.MS and the data is there in Excel, it's just formatted the cell in an alternative way to what I would like.

 ......

Cheers again

Mitch

---

I was thinking about something like the snipped added below. My version is very simple, but I think that it shows how it can be done. You need to have Excel on your machine. (Disclaimer: This is my very first use of the Report Generation Toolkit in LabVIEW, so maybe there is a better way to do it)

I made the VI labels visible, so that you know what function was used, if the Toolkit is not included in your LabVIEW license. The same thing can be done with ActiveX automation. If you are not familiar with it, there are a lot of examples in the forums about generating Excel files via ActiveX.

Edit: Attached is the Excel file generated from this code

[Mitch_Peplow]

Evening all,

Apologies for the belated response but I had half day holiday on Friday so didn't see any of these posts.

Thanks to all that have posted, much appreciated and given me real food for thought.

The idea of writing a simple DBL to the Excel file seems so elegant and simple, and upon a quick trial before I left on Friday seemed to accomplish what I was after which is great, however I would like to know how excel knows to interpret this as a date rather than a static numerical data when no formatting options were sent i.e. I didn't use the report generation toolkit to format the cells to custom time options?

Cheers again

Mitch

[Stuart.Parkinson]

Excel won't know unless you tell it. To Excel, time is just a number - you have to format the cell or column (select it and type Ctrl-1) and select date or time or a custom time format like 'hh:mm:ss.000' and then it will display it as a time instead of just a number.

EDIT - no way I know of automating this unless you are a wiz with excel macros and templates which I am not! 

[Mitch_Peplow]

Morning all,

This is all really good info and all the methods seem to work so I’m going to sit down at some point this week and see if I can resolve the issue. Really it boils down to the fact that there's no easy way to tell Excel what format the text should be in when its being written unless you use the specific report generation toolkit.

Regarding automation of the process, it does appear that the report generation toolkit does this, however it also opens the file automatically after writing/saving with seemingly no obvious way to stop this.

The ActiveX properties may achieve the same outcome without opening the file but I need to have a play around. Unless somebody in the community has already managed to achieve this to save me some time perusing the endless list of properties available?

I’ll update the post when I have some more information.

Cheers all

Mitch