Expressing Excel formula in Java (decimal to time interpretation)

Question

Hi, I am converting an excel sheet formula to java but I can't understand how excel manages to take the following: 0.22 Applies a formula: =TEXT(R5/14, "h:mm") and somehow arrives at: 0.22

Again if I provide: 2.8 it arrives at 4.48 Can someone please explain to me how it does this. I have read a little regarding decimal and I understand the conversion but this hasn't yet helped to explain the above.

Answer 1

Yeah it is a bit goofy. Take the /14 out and that helps. Basically 1=1 day so R5 is expressed in 14ths of a day. You could probably do

int msInADay= 86400000;
Time value = new Time(R5/14 * msInADay);

but it is untested.

Answer 2

Excel stores datetime values as:

• The number to the left of the decimal represents the number of days since January 1, 1900
• The number to the right of the decimal represents the fractional portion of a 24-hour day

In your example, you are converting a decimal to a textual representation of the hour and minute portions of the datetime value.

Working through your first formula, 0.22 divided by 14 (why are you doing this?) equals 0.015714286. If you then apply this fraction against a 24-hour day (multiply by 1440 minutes), it equals 22 minutes and some change (i.e. "0:22").

Working through your second formula, 2.8 divided by 14 equals 0.2. Multiplied by 1440, it equals 288 minutes, which is 4 hours and 48 minutes (i.e. "4:48").

Answer 3

Thanks Creedence.

It works with: double r = 0.22; double driveTime = (r / 14) * 1440;

Answer 4

A very good documentation of Spreadsheet-internals can be found in the OpenFormula specififcation (which documents/defines OpenOffice-Calc).

http://www.oasis-open.org/committees/documents.php?wg_abbrev=office-formula

If you seek a Java-Library to evaluate Excel-Formulas, either Apache-POI or Pentaho's LibFormula may be helpful:

POI: http://poi.apache.org/

LibFormula: http://sourceforge.net/project/showfiles.php?group_id=51669&package_id=213669

Answer 5

Or the Abacus Formula Compiler for Java, which allows you to compile the formulas in Excel sheets right down to Java byte code for fast and easy calling from your Java apps (can compile at run-time without the JDK).

http://www.formulacompiler.org/