Bug 52169

Summary: Date arithmetic on textual cells
Product: POI Reporter: rene.becker
Component: HSSFAssignee: POI Developers List <dev>
Status: NEW ---    
Severity: enhancement CC: r_jacobs
Priority: P2    
Version: 3.17-FINAL   
Target Milestone: ---   
Hardware: PC   
OS: Windows XP   
Attachments: Date arithmetic use case

Description rene.becker 2011-11-11 06:57:37 UTC
Created attachment 27925 [details]
Date arithmetic use case

Excel directly supports arithmetic operations on non-numeric textcells.
A1='2012/01/02
A2=A1-1
will display A2 as '2012/01/01'

This is not possible with POI, also the DATEVALUE() function is currently not offered.

Attached is a testcase, which exemplifies the above use case.
Comment 1 Ralph Jacobs 2018-06-06 09:21:38 UTC
Same applies to date or time constants as arguments of the VALUE function (VALUE("11:45"). They will give a #VALUE! error on evaluation.
see org.apache.poi.ss.formula.functions.Value.convertTextToNumber(String)

Missing implementations of specialized functions DATEVALUE and TIMEVALUE, which would offer a workaround for the VALUE function in case of date/time constants.