Bug 52169 - Date arithmetic on textual cells
Summary: Date arithmetic on textual cells
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.17-FINAL
Hardware: PC Windows XP
: P2 enhancement (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2011-11-11 06:57 UTC by rene.becker
Modified: 2021-10-08 20:10 UTC (History)
1 user (show)



Attachments
Date arithmetic use case (5.24 KB, application/x-zip-compressed)
2011-11-11 06:57 UTC, rene.becker
Details

Note You need to log in before you can comment on or make changes to this bug.
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.
Comment 2 PJ Fanning 2021-07-28 16:59:32 UTC
DATEVALUE function is in recent versions of POI (eg POI 5.0.0). TIMEVALUE is not implemented yet.
Comment 3 PJ Fanning 2021-10-08 20:10:51 UTC
TIMEVALUE is in v5.1.0