Item11003: Spreadsheet TIME* functions do not work properly with dates before 1970.

Priority: Normal
Current State: Closed
Released In: 2.0.0
Target Release: major

Applies To: Extension
Component: SpreadSheetPlugin
Branches: master

Reported By: RobertGerlach
Waiting For:
Last Change By: GeorgeClark

• %CALC{"$TIMEDIFF($TIME(1950/1/1), $TIME(2011/1/1), year)"}% returns -38, should be 61.
• %CALC{"$TIME(1950/1/1)"}% should return a negative number, but it does not.

diff --git a/lib/Foswiki/Plugins/SpreadSheetPlugin/Calc.pm b/lib/Foswiki/Plugins/SpreadSheetPlugin/Calc.pm
index ca90e8d..1ea92ff 100644
--- a/lib/Foswiki/Plugins/SpreadSheetPlugin/Calc.pm
+++ b/lib/Foswiki/Plugins/SpreadSheetPlugin/Calc.pm
@@ -845,7 +845,7 @@ s/\$([A-Z]+)$escToken([0-9]+)\((.*?)$escToken\2\)/&doFunc($1,$3)/geo;
     }
     elsif ( $theFunc =~ /^(FORMATTIME|FORMATGMTIME)$/ ) {
         my ( $time, $str ) = split( /,\s*/, $theAttr, 2 );
-        if ( $time =~ /([0-9]+)/ ) {
+        if ( $time =~ /(-?[0-9]+)/ ) {
             $time = $1;
         }
         else {
@@ -922,8 +922,8 @@ s/\$([A-Z]+)$escToken([0-9]+)\((.*?)$escToken\2\)/&doFunc($1,$3)/geo;
         $time  = 0  unless ($time);
         $value = 0  unless ($value);
         $scale = "" unless ($scale);
-        $time  =~ s/.*?([0-9]+).*/$1/o      || 0;
-        $value =~ s/.*?(\-?[0-9\.]+).*/$1/o || 0;
+        $time  =~ s/.*?(-?[0-9]+).*/$1/o      || 0;
+        $value =~ s/.*?(-?[0-9\.]+).*/$1/o || 0;
         $value *= 60            if ( $scale =~ /^min/i );
         $value *= 3600          if ( $scale =~ /^hou/i );
         $value *= 3600 * 24     if ( $scale =~ /^day/i );
@@ -939,8 +939,8 @@ s/\$([A-Z]+)$escToken([0-9]+)\((.*?)$escToken\2\)/&doFunc($1,$3)/geo;
         $scale ||= '';
         $time1 = 0 unless ($time1);
         $time2 = 0 unless ($time2);
-        $time1 =~ s/.*?([0-9]+).*/$1/o || 0;
-        $time2 =~ s/.*?([0-9]+).*/$1/o || 0;
+        $time1 =~ s/.*?(-?[0-9]+).*/$1/o || 0;
+        $time2 =~ s/.*?(-?[0-9]+).*/$1/o || 0;
         $result = $time2 - $time1;
         $result /= 60            if ( $scale =~ /^min/i );
         $result /= 3600          if ( $scale =~ /^hou/i );
@@ -1436,8 +1436,10 @@ m|([0-9]{1,2})[-\s/]+([A-Z][a-z][a-z])[-\s/]+([0-9]{4})[-\s/]+([0-9]{1,2}):([0-9
         $day  = $1;
         $mon  = $mon2num{$2} || 0;
         $year = $3;
-        $year += 100 if ( $year < 80 );    # "05"   --> "105" (leave "99" as is)
-        $year -= 1900 if ( $year >= 1900 );    # "2005" --> "105"
+       if ($year < 100) {
+           $year += 2000 if ( $year < 80 );    # "05"   --> "2005"
+           $year += 1900 if ( $year >= 80 );   # "85"   --> "1985"
+       }
     }
     elsif ( $theText =~
 m|([0-9]{4})[-/\.]([0-9]{1,2})[-/\.]([0-9]{1,2})[-/\.\,\s]+([0-9]{1,2})[-\:/\.]([0-9]{1,2})[-\:/\.]([0-9]{1,2})|
@@ -1445,7 +1447,7 @@ m|([0-9]{4})[-/\.]([0-9]{1,2})[-/\.]([0-9]{1,2})[-/\.\,\s]+([0-9]{1,2})[-\:/\.](
     {
 
         # "2003/12/31 23:59:59", "2003-12-31-23-59-59", "2003.12.31.23.59.59"
-        $year = $1 - 1900;
+        $year = $1;
         $mon  = $2 - 1;
         $day  = $3;
         $hour = $4;
@@ -1458,7 +1460,7 @@ m|([0-9]{4})[-/\.]([0-9]{1,2})[-/\.]([0-9]{1,2})[-/\.\,\s]+([0-9]{1,2})[-\:/\.](
     {
 
         # "2003/12/31 23:59", "2003-12-31-23-59", "2003.12.31.23.59"
-        $year = $1 - 1900;
+        $year = $1;
         $mon  = $2 - 1;
         $day  = $3;
         $hour = $4;
@@ -1467,7 +1469,7 @@ m|([0-9]{4})[-/\.]([0-9]{1,2})[-/\.]([0-9]{1,2})[-/\.\,\s]+([0-9]{1,2})[-\:/\.](
     elsif ( $theText =~ m|([0-9]{4})[-/]([0-9]{1,2})[-/]([0-9]{1,2})| ) {
 
         # "2003/12/31", "2003-12-31"
-        $year = $1 - 1900;
+        $year = $1;
         $mon  = $2 - 1;
         $day  = $3;
     }
@@ -1478,8 +1480,10 @@ m|([0-9]{4})[-/\.]([0-9]{1,2})[-/\.]([0-9]{1,2})[-/\.\,\s]+([0-9]{1,2})[-\:/\.](
         $year = $3;
         $mon  = $1 - 1;
         $day  = $2;
-        $year += 100 if ( $year < 80 );    # "05"   --> "105" (leave "99" as is)
-        $year -= 1900 if ( $year >= 1900 );    # "2005" --> "105"
+       if ($year < 100) {
+           $year += 2000 if ( $year < 80 );    # "05"   --> "2005"
+           $year += 1900 if ( $year >= 80 );   # "85"   --> "1985"
+       }
     }
     else {