i want to import a time field from a excel file using excell buffer table. if in excel the time field value is 09:30:00, when i import the file, in the excell buffer table value is 0,40486111111111 i think that excell buffer transform the value to a text value. but i need the time...is possible do something.
thank you
0
Answers
rExcelBuffer.OpenBook(FileRoot,rExcelBuffer.SelectSheetsName(fileroot));
rExcelBuffer.ReadSheet();
i don´t know where i have to put the code, if i have to put before readsheet how can i change the field format. finally i think that i have to change the function readsheet and put to code in....any observation please
thank yo so much
"Never memorize what you can easily find in a book".....Or Mibuso
My Blog
thank you so much ](*,) ](*,)
(before asking him, try if it solves your problem with a test file)
"Never memorize what you can easily find in a book".....Or Mibuso
My Blog
regards
If you use the below function in excel and enter you number you will get your time
TIMEVALUE function
Show All
Hide All
Returns the decimal number of the time represented by a text string. The decimal number is a value ranging from 0 (zero) to 0.99999999, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.).
Hope this helps
Steve
"Never memorize what you can easily find in a book".....Or Mibuso
My Blog
Call the function below as this example for test:
IF TrasformaOreExcelInTime(0.382638889,VariableTimeToReceive) THEN
MESSAGE('%1',VariableTimeToReceive);
Create a function like this:
TrasformaOreExcelInTime(ValoreDecimaleParm : Decimal;VAR ValoreTime : Time) : Boolean
IF (ValoreDecimaleParm < 0) OR (ValoreDecimaleParm > 1) THEN
EXIT(FALSE);
IF (ValoreDecimaleParm = 0) THEN BEGIN
ValoreTime := 0T;
RisultatoEvaluateLoc := TRUE;
END ELSE BEGIN
TotaleMillisecondiLoc := ROUND((86400000 * ValoreDecimaleParm),1,'=');
OreLoc := TotaleMillisecondiLoc DIV 3600000;
Intero2Loc := OreLoc * 3600000;
MillisecondiMinutiLoc := TotaleMillisecondiLoc - Intero2Loc;
MinutiLoc := MillisecondiMinutiLoc DIV 60000;
Intero3Loc := MinutiLoc * 60000;
MillisecondiSecondiLoc := MillisecondiMinutiLoc -Intero3Loc;
SecondiLoc := MillisecondiSecondiLoc DIV 1000;
InteroOreMinutiSecondiLoc := (OreLoc * 10000) + (MinutiLoc * 100) + SecondiLoc;
TestOreMinutiSecondiLoc := FORMAT(InteroOreMinutiSecondiLoc);
if InteroOreMinutiSecondiLoc < 99999 then begin
TestOreMinutiSecondiLoc := '0' + TestOreMinutiSecondiLoc;
end;
RisultatoEvaluateLoc := EVALUATE(ValoreTime,TestOreMinutiSecondiLoc);
END;
EXIT(RisultatoEvaluateLoc);
Declare Local Variable as:
Name DataType Subtype Length
TotaleMillisecondiLoc Integer
MillisecondiMinutiLoc Integer
OreLoc Integer
Intero2Loc Integer
MinutiLoc Integer
MillisecondiSecondiLoc Integer
Intero3Loc Integer
SecondiLoc Integer
InteroOreMinutiSecondiLoc Integer
TestOreMinutiSecondiLoc Text 30
RisultatoEvaluateLoc Boolean
Declare Parameters as:
Var Name DataType Subtype Length
No ValoreDecimaleParm Decimal
Sì ValoreTime Time
Dont forget the return value as boolean!
Sorry, all the variabels are in Italian. I was too lazy to translate!
Bye, bye.
=D> =D> =D> =D> =D> =D> =D> =D> =D>
The function is taken from Universal Excel Importer from mibuso. In Codeunit99991 you will find the function and how to use it to read DATETIME from Excel Buffer table.
Dynamics NAV, MS SQL Server, Wherescape RED;
PRINCE2 Practitioner - License GR657010572SG
GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03