How to extract a date from Excel into Scilab?

user_15897147 0
Viewed 38

I want to use data from an Excel doc in Scilab. And one of the data that i want to use are dates in DD/MM/YYYY.

But I have a problem when I want to read it in Scilab. For exemple, if in the sheet my date is 20/11/2020, in Scilab it converts to 44155.

I tried to use the datevec() function, but it returns me 120. 11. 21. 0. 0. 0.

The problem for me comes from the data exctraction. I don't know what Scilab does when it reads the Excel doc. And I don't want to change the format of the date on Excel, so I'm kinda stuck now.

If you have a solution, please help me T-T.

scilab excel date
1 Answers

As already noted in comments, in cells with a date format, actual dates are stored as numbers, in Excel as in other spreadsheet softwares like Libreoffice/Calc and so. Once you have read the spreadsheet with scilab and you have got serialized dates as a single number for each one, you must convert them with the following code, that uses your 2020-11-20 example stored as 44155:

--> [Y,M,D] = datevec(44155+693960) 
 Y  = 
   2020.
 M  = 
   11.
 D  = 
   20.

As a general rule, add the constant 693960 to your numerical dates from Excel. This constant is just the shift between the Excel's origin for dates, and the Scilab/datenum/datevec one.

datevec is "vectorized". That means that if you have read a whole column of dates in Excel, you have got a whole column of corresponding numbers. Then you can provide them to datevec() with a single call, as in the following:

--> N = (44155:44175)';
--> [Y,M,D] = datevec(N+693962); [N Y M D]
 ans  =
   44155.   2020.   11.   22.
   44156.   2020.   11.   23.
   44157.   2020.   11.   24.
   44158.   2020.   11.   25.
   44159.   2020.   11.   26.
   44160.   2020.   11.   27.
   44161.   2020.   11.   28.
   44162.   2020.   11.   29.
   44163.   2020.   11.   30.
   44164.   2020.   12.   1. 
   44165.   2020.   12.   2. 
   44166.   2020.   12.   3. 
   44167.   2020.   12.   4. 
   44168.   2020.   12.   5. 
   44169.   2020.   12.   6. 
   44170.   2020.   12.   7. 
   44171.   2020.   12.   8. 
   44172.   2020.   12.   9. 
   44173.   2020.   12.   10.
   44174.   2020.   12.   11.
   44175.   2020.   12.   12.

and then to print (or get) dates as strings in standard international format:

--> mprintf(""%d-%02d-%02d\n", Y,M,D) // or msprintf(..) to get strings instead of printing
2020-11-22
2020-11-23
2020-11-24
2020-11-25
2020-11-26
2020-11-27
2020-11-28
2020-11-29
2020-11-30
2020-12-01
2020-12-02
2020-12-03
2020-12-04
2020-12-05
2020-12-06
2020-12-07
2020-12-08
2020-12-09
2020-12-10
2020-12-11
2020-12-12
user_150741990
Related