Reading date from Excel with LabWindows CVI
Kurt Jakobsen
I am trying to read a date cell from Excel 2003 with CVI 7.0.0.
Using 'CA_VariantHasDate' give error.
Using 'CA_VariantGetDate' give error.
The only thing that seems to work is 'CA_VariantGetDouble' but the double value returned does not make any sense to me.
All help will be most appreciated.
Best regards
Kurt Jakobsen
ebalci
Could you send the value that you read from Excel as double?It may look meaningless but the date and time values may be kept as a single number giving, for example, the number of seconds since some reference time in the past.CVI has some time format conversion functions some of which are in the ANSI library. That value you got from Excel might be meaningful for one of them.Just "surf" a little in the function libraries.
If you post the value I can help more.
Kurt Jakobsen
Thanks for your reply, I've tried a bit further without result, so I've attached my project. Make your project path C:\Source\PdYieldToDb and it should work. The excel file is Test.xls The call to the cell is from the main function.
Thanks again for your help!
best regards
Kurt
PdYieldToDb1.zip:
http://forums.ni.com/attachments/ni/180/28851/1/PdYieldToDb1.zip
Kurt Jakobsen
the double value returned is 39184. The value in the date cell is 12.04.2007.
best regards
Kurt
ebalci
I just need the double value, I mean a single number. Not the whole project.I cannot run your project because I do not have CVI installed in this machine.You can simply debug your code and copy the number you got from Excel into the post.
jr_2005
JR
ebalci
A little search in CVI, Excel help and Google gives the following results:
- What you get from Excel (39184) is actually a date in Excel serial format.
- It gives the number of days since 1 January 1900. 39184 is meaningful since a rough calculation 39184/365 = 107.353... Which means 2007 is about 107 years after 1900.
- CVI can convert the number of "seconds" since 1 Jan 1900 to a day-month-year (DMY) format.
- The following link has the function to convert Excel serial format to DMY format: <a href="http://www.codeproject.com/datetime/exceldmy.asp" target="_blank">http://www.codeproject.com/datetime/exceldmy.asp</a>
The link has the source code in it. But it would give compiler errors in CVI when copied directly. I tried to "clean" the code in a text editor (since I do not have CVI right now) and I hope it will compile. I paste the code below.You paste it in a file and just call it with necessary parameters.
Please let me know if something goes wrong so I can edit the post.
Note: You may also check that link to verify the relation between Excel and Gregorian calendar formats <a href="http://www.calendarhome.com/converter/" target="_blank">http://www.calendarhome.com/converter/</a>
void ExcelSerialDateToDMY(int nSerialDate, int *nDay, int *nMonth, int *nYear){ int l, n, i, j; // Excel/Lotus 123 have a bug with 29-02-1900. 1900 is not a // leap year, but Excel/Lotus 123 think it is... if (nSerialDate == 60) { (*nDay) = 29; (*nMonth) = 2; (*nYear) = 1900;
return; } else if (nSerialDate < 60) { // Because of the 29-02-1900 bug, any serial date // under 60 is one off... Compensate. nSerialDate++; }
// Modified Julian to DMY calculation with an addition of 2415019 l = nSerialDate + 68569 + 2415019; n = (int)(( 4 * l ) / 146097); l = l - (int)(( 146097 * n + 3 ) / 4); i = (int)(( 4000 * ( l + 1 ) ) / 1461001); l = l - (int)(( 1461 * i ) / 4) + 31; j = (int)(( 80 * l ) / 2447); (*nDay) = l - (int)(( 2447 * j ) / 80); l = (int)(j / 11); (*nMonth) = j + 2 - ( 12 * l ); (*nYear) = 100 * ( n - 49 ) + i + l;} Message Edited by ebalci on 04-17-2007 04:28 PM
ebalci
I am also glad that it worked :smileywink: