LabVIEW

cancel
Showing results for 
Search instead for 
Did you mean: 

excel format problem

Solved!
Go to solution

hi, i am facing a very minor  but significant problem.

The problem is with the excel xlsx file format changing when i am saving the data in book2.xlsx file.

As you can see in the file from date 25th till the date 31st the format is dd-mm-yyyy hh:mm:ss but it changes from date 1st to 12th to dd-mm-yyyy hh:mm and from 13th again comes to general format which is dd-mm-yyyy hh:mm:ss

Though while i seeing the file their is no problem but while reading the file in the labview  it changes in some long decimal number like 41275.1667 only in case of this format dd-mm-yyyy hh:mm

i have changed the format to general of some of the cells just to notify you.

That i have done by changing the cell format of that cell to general .

my question is complicated but you will easily analysis th problem after reading the file -Book2.xlsx

 

so i need help to save the file in just single format that is general format,thanks.

Download All
0 Kudos
Message 1 of 13
(5,228 Views)

When I run your VI, it runs just fine creating a new spreadsheet with dates in the format of 12/25/12  0:30.  I open your excel sheet and it shows a variety of formats with with some being custom at m/d/yy hh:mm, and others being "general".

 

First question, are you doing european date formats where the date comes before the month?  My guess is that you have a mismatch of date formats between how LabVIEW is formatting strings and how Excel is interpreting them.  Check all your date format settings in Windows, Excel, and LabVIEW.  If you find discrepancies, you may need to be more specific in your date format codes in Excel.  Look at the help file for time/date format codes.

 

I'm wondering why your VI looks so complicated.  First, you are using a while loop when it looks like you only need a For Loop, that way you don't need to worry about a while loop stop condition.  Second, your array manipulation seems more complicated than necessary.  Rather than delete from array and insert into array, you should only need to Replace Array Subset.

 

Third, and most importantly, the string to time to date cluster, to time, to string to Build Array, all seems unnecessarily complicated.  What are you really tyring to do?  It seems like there are much easier ways to do it with a fraction of the nodes and wiring.

0 Kudos
Message 2 of 13
(5,201 Views)

It is not a formatting issue, in your loop you always add one day keeping the same month and same year. This seems to be creating the issue. Just convert your timestamp to double, add loop index time 86400 (number od seconds in one day) to this value and convert back to timestamp. No need to convert to date record. I agree with Ravens Fan that your code can be simplified a lot.

 

Ben64

0 Kudos
Message 3 of 13
(5,191 Views)

There is also a formatting issue in your Excel file, the dates are all displayed in the same format in LabVIEW but from jan 1st to Jan 12th are displayed differently in Excel.

 

Ben64

0 Kudos
Message 4 of 13
(5,179 Views)

hi, raven & ben thanks for reply.

Thanks for correcting me  i was fool using while loop instead of making it simple using for loop  

i have not tryed the simple way you are telling me.Though i understood some points you discussed in above reply but not completely

Can u please help me out ,your help will be appreciated.

i have corrected some of the things in code.

0 Kudos
Message 5 of 13
(5,174 Views)

hi ben,

the format is changing automatically when code runs and save the data in file the format is automatically changed 

see i just created new xlsx file 

0 Kudos
Message 6 of 13
(5,164 Views)

Answer my questions about what type of date format you are using, if it is european and whether Excel and/or Windows is set that way.

0 Kudos
Message 7 of 13
(5,154 Views)

Here I cleaned up your code.

 

Try running this VI 2 times, once with the boolean pressed, the other without.  See which works.

0 Kudos
Message 8 of 13
(5,150 Views)
Solution
Accepted by topic author ransher

I have the same problem with the dates from jan 1st to jan 12th. It is Excel autoformatting the dd-mm-YYYY H:M format to mm/dd/yyyy h:mm. (I have a French version of Excel 2010). Up to jan 12 Excel uses one of its pre-defined format and switches day and month and use 12h time.

 

This autoformatting can be disabled by adding a space in front of the date time string. I can't see how RavensFan implemented his version (I don't have LV2017) but here's my simplified version of your code.

Excel Date Format.png

Ben64

 

Message 9 of 13
(5,144 Views)

i don't know how to check date format.

i have corrected the code somehow but not completely. i have to increment date in every row.

The code you send me is in version 2017 and i am using version 2014 and 2016

can you please send me code in 2014 or 2016 if you don't mind,thanks. 

0 Kudos
Message 10 of 13
(5,137 Views)