Bug 63819 - Spreadsheet DATEVALUE function missing
Summary: Spreadsheet DATEVALUE function missing
Status: NEW
Alias: None
Product: POI
Classification: Unclassified
Component: SS Common (show other bugs)
Version: 4.0.x-dev
Hardware: PC Linux
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords: PatchAvailable
Depends on:
Blocks:
 
Reported: 2019-10-09 08:27 UTC by Petr Michálek
Modified: 2019-10-11 05:29 UTC (History)
1 user (show)



Attachments
Diff with DATEVALUE function (12.26 KB, patch)
2019-10-10 09:52 UTC, Petr Michálek
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description Petr Michálek 2019-10-09 08:27:36 UTC
Please implement function DATEVALUE

https://support.office.com/en-us/article/DATEVALUE-function-DF8B07D4-7761-4A93-BC33-B7471BBFF252
https://help.libreoffice.org/Calc/DATEVALUE

It could by done like this: (adding sublass class in org.apache.poi.ss.formula.functions.DateFunc)


public final class DateFunc extends Fixed3ArgFunction {

   ....

    public static final Function DATEVALUE = new Fixed1ArgFunction() {
            public ValueEval evaluate(int srcRowIndex, int srcColumnIndex, ValueEval arg0) {
                try {
                    ValueEval ve = OperandResolver.getSingleValue(arg0, srcRowIndex, srcColumnIndex);
                    String arg = OperandResolver.coerceValueToString(ve);

                    StringTokenizer t = new StringTokenizer(arg, "-/");
                    String A = t.nextToken();
                    String B = t.nextToken();
                    String C = t.hasMoreTokens() ? t.nextToken() : null;
                    if (B.length() > 3) {
                        throw new EvaluationException(ErrorEval.VALUE_INVALID);
                    }

                    int year;
                    int month;
                    int day;

                    int a = Integer.valueOf(A);
                    if (StringUtils.isNumeric(B)) {
                        int b = Integer.valueOf(B);
                        int c = Integer.valueOf(C);
                        month = b;
                        if (a >= 1900) { // =DATEVALUE("2011/02/23")
                            year = a;
                            day = c;
                        } else { // =DATEVALUE("8/22/2011")
                            year = c;
                            day = a;
                        }
                    } else {

                        String monthName = B.toLowerCase();
                        String months[] = new DateFormatSymbols().getMonths();
                        for (month = 0; month < 12; month++) {
                            if (months[month].startsWith(monthName)) {
                                break;
                            }
                        }
                        if (month >= 12) {
                            throw new EvaluationException(ErrorEval.VALUE_INVALID);
                        }
                        month++;

                        if (C == null) {
                            // =DATEVALUE("22-MAY")
                            GregorianCalendar gc = new GregorianCalendar();
                            year = gc.get(Calendar.YEAR);
                            day = a;
                        } else {
                            // =DATEVALUE("22-MAY-2011")
                            year = Integer.valueOf(C);
                            day = a;
                        }
                    }

                    return new NumberEval(evaluate(year, month, day));
                } catch (EvaluationException e) {
                    return e.getErrorEval();
                }
            }
    };

   ...

}
Comment 1 Nick Burch 2019-10-09 14:34:36 UTC
Thanks for this!

Would you be able to work out a little test case for this? In code, in pseudo-code, or as an extension to one of the existing date function unit tests? That way, we can verify it stays working into the future too
Comment 2 Petr Michálek 2019-10-10 09:52:52 UTC
Created attachment 36817 [details]
Diff with DATEVALUE function

Diff file with function DATEVALUE and little testcase is attached. Please look at this.