Converting Decimal to Time

Business Central/NAV Developers Forum
1

In connection with a project I am importing from Excel using the Excel Buffer table. Here I’m importing time entries, with a from and to time.

As it doesn’t work to convert the time value in Excel directly from the “Cell Value as Text” in the Excel Buffer table then I was trying to do like this:

TimeTxt := ExcelBuf."Cell Value as Text";
IF EVALUATE(TimeTotal,TimeTxt) THEN;
TimeTotal := ROUND(TimeTotal * 24,0.01);
TimeTxt := FORMAT(TimeTotal);
IF EVALUATE(TimeImport."Time From",TimeTxt) THEN;

It works partly, but when time for example is 20:30, then the value coming from the Excel Buffer is 0,85416666666666663 - recalculated this is 20,5 and when converted it’s becoming 20:05 (not 20:30).

Does anyone have a better way to convert times in the Excel String format to a NAV time record?

2

Hi Erik,

I haven’t converted from Excel but the logic is the same. The issue here is the string being passed to the EVALUATE - the function is expecting HHMMSS and is tring to be clever stripping the decimal place

e.g.

0830 convert to 08:30:00

083 converts to 08:03:00

So you will need to format the string by rounding down to get the hour. Then take the rounded hour from the time value and multply by 60 to get the minutes.

If you need the code, let me know and I can post it.

3

Thanks, with this and what I had been able to find in other posts then I was able to do the math!

4

Thanks you for the post.

Watch Resident Evil: Afterlife Online Free