#!/usr/bin/perl use strict; use warnings; # Convert between Excel serial dates and Time::Moment use Time::Moment; use constant EXCEL_EPOCH => Time::Moment->from_string('1899-12-30T00Z')->rd; sub moment_from_excel { @_ == 1 or die q/Usage: moment_from_excel(date)/; my ($date) = @_; return Time::Moment->from_rd($date + ($date < 61), epoch => EXCEL_EPOCH); } sub moment_to_excel { @_ == 1 or die q/Usage: moment_to_excel(moment)/; my ($moment) = @_; my $date = $moment->rd - EXCEL_EPOCH; return $date - ($date < 61); } my @tests = ( [ '1899-12-31T00:00:00Z', 0 ], [ '1900-01-01T00:00:00Z', 1 ], [ '1900-02-27T00:00:00Z', 58 ], [ '1900-02-28T00:00:00Z', 59 ], [ '1900-03-01T00:00:00Z', 61 ], [ '1900-03-02T00:00:00Z', 62 ], [ '1969-12-31T23:59:58Z', 25568.9999768519 ], [ '1969-12-31T23:59:58.500Z', 25568.9999826389 ], [ '1969-12-31T23:59:58.800Z', 25568.9999861111 ], [ '1969-12-31T23:59:58.900Z', 25568.9999872685 ], [ '1969-12-31T23:59:58.980Z', 25568.9999881944 ], [ '1969-12-31T23:59:58.990Z', 25568.9999883102 ], [ '1969-12-31T23:59:58.998Z', 25568.9999884028 ], [ '1969-12-31T23:59:58.999Z', 25568.9999884143 ], [ '1969-12-31T23:59:59Z', 25568.9999884259 ], [ '1969-12-31T23:59:59.001Z', 25568.9999884375 ], [ '1969-12-31T23:59:59.002Z', 25568.9999884490 ], [ '1969-12-31T23:59:59.010Z', 25568.9999885417 ], [ '1969-12-31T23:59:59.020Z', 25568.9999886574 ], [ '1969-12-31T23:59:59.100Z', 25568.9999895834 ], [ '1969-12-31T23:59:59.200Z', 25568.9999907408 ], [ '1969-12-31T23:59:59.300Z', 25568.9999918982 ], [ '1969-12-31T23:59:59.400Z', 25568.9999930556 ], [ '1969-12-31T23:59:59.490Z', 25568.9999940973 ], [ '1969-12-31T23:59:59.499Z', 25568.9999942014 ], [ '1969-12-31T23:59:59.500Z', 25568.9999942130 ], [ '1969-12-31T23:59:59.501Z', 25568.9999942245 ], [ '1969-12-31T23:59:59.510Z', 25568.9999943287 ], [ '1969-12-31T23:59:59.600Z', 25568.9999953704 ], [ '1969-12-31T23:59:59.700Z', 25568.9999965278 ], [ '1969-12-31T23:59:59.800Z', 25568.9999976852 ], [ '1969-12-31T23:59:59.900Z', 25568.9999988426 ], [ '1969-12-31T23:59:59.980Z', 25568.9999997686 ], [ '1969-12-31T23:59:59.990Z', 25568.9999998843 ], [ '1969-12-31T23:59:59.998Z', 25568.9999999768 ], [ '1969-12-31T23:59:59.999Z', 25568.9999999885 ], [ '1970-01-01T00:00:00Z', 25569 ], [ '1970-01-01T00:00:00.001Z', 25569.0000000115 ], [ '1970-01-01T00:00:00.002Z', 25569.0000000232 ], [ '1970-01-01T00:00:00.010Z', 25569.0000001157 ], [ '1970-01-01T00:00:00.020Z', 25569.0000002314 ], [ '1970-01-01T00:00:00.100Z', 25569.0000011574 ], [ '1970-01-01T00:00:00.200Z', 25569.0000023148 ], [ '1970-01-01T00:00:00.300Z', 25569.0000034722 ], [ '1970-01-01T00:00:00.400Z', 25569.0000046296 ], [ '1970-01-01T00:00:00.490Z', 25569.0000056713 ], [ '1970-01-01T00:00:00.499Z', 25569.0000057755 ], [ '1970-01-01T00:00:00.500Z', 25569.0000057870 ], [ '1970-01-01T00:00:00.501Z', 25569.0000057986 ], [ '1970-01-01T00:00:00.510Z', 25569.0000059027 ], [ '1970-01-01T00:00:00.600Z', 25569.0000069444 ], [ '1970-01-01T00:00:00.700Z', 25569.0000081018 ], [ '1970-01-01T00:00:00.800Z', 25569.0000092592 ], [ '1970-01-01T00:00:00.900Z', 25569.0000104166 ], [ '1970-01-01T00:00:00.980Z', 25569.0000113426 ], [ '1970-01-01T00:00:00.990Z', 25569.0000114583 ], [ '1970-01-01T00:00:00.998Z', 25569.0000115510 ], [ '1970-01-01T00:00:00.999Z', 25569.0000115625 ], [ '1970-01-01T00:00:01Z', 25569.0000115741 ], [ '1970-01-01T00:00:01.001Z', 25569.0000115857 ], [ '1970-01-01T00:00:01.002Z', 25569.0000115972 ], [ '1970-01-01T00:00:01.010Z', 25569.0000116898 ], [ '1970-01-01T00:00:01.020Z', 25569.0000118056 ], [ '1970-01-01T00:00:01.100Z', 25569.0000127315 ], [ '1970-01-01T00:00:01.200Z', 25569.0000138889 ], [ '9999-12-31T23:59:58Z', 2958465.9999768520 ], [ '9999-12-31T23:59:58.500Z', 2958465.9999826388 ], [ '9999-12-31T23:59:58.800Z', 2958465.9999861112 ], [ '9999-12-31T23:59:58.900Z', 2958465.9999872684 ], [ '9999-12-31T23:59:58.980Z', 2958465.9999881946 ], [ '9999-12-31T23:59:58.990Z', 2958465.9999883100 ], [ '9999-12-31T23:59:58.998Z', 2958465.9999884027 ], [ '9999-12-31T23:59:58.999Z', 2958465.9999884143 ], [ '9999-12-31T23:59:59Z', 2958465.9999884260 ], [ '9999-12-31T23:59:59.001Z', 2958465.9999884376 ], [ '9999-12-31T23:59:59.002Z', 2958465.9999884493 ], [ '9999-12-31T23:59:59.010Z', 2958465.9999885415 ], [ '9999-12-31T23:59:59.020Z', 2958465.9999886574 ], [ '9999-12-31T23:59:59.100Z', 2958465.9999895832 ], [ '9999-12-31T23:59:59.200Z', 2958465.9999907408 ], [ '9999-12-31T23:59:59.300Z', 2958465.9999918980 ], [ '9999-12-31T23:59:59.400Z', 2958465.9999930556 ], [ '9999-12-31T23:59:59.490Z', 2958465.9999940973 ], [ '9999-12-31T23:59:59.499Z', 2958465.9999942016 ], [ '9999-12-31T23:59:59.500Z', 2958465.9999942128 ], [ '9999-12-31T23:59:59.501Z', 2958465.9999942244 ], [ '9999-12-31T23:59:59.510Z', 2958465.9999943287 ], [ '9999-12-31T23:59:59.600Z', 2958465.9999953704 ], [ '9999-12-31T23:59:59.700Z', 2958465.9999965276 ], [ '9999-12-31T23:59:59.800Z', 2958465.9999976852 ], [ '9999-12-31T23:59:59.900Z', 2958465.9999988424 ], [ '9999-12-31T23:59:59.980Z', 2958465.9999997686 ], [ '9999-12-31T23:59:59.990Z', 2958465.9999998841 ], [ '9999-12-31T23:59:59.998Z', 2958465.9999999767 ], [ '9999-12-31T23:59:59.999Z', 2958465.9999999884 ], ); use Test::More 0.88; use Test::Number::Delta 1.06 relative => 1E-10; foreach my $test (@tests) { my ($string, $date) = @$test; my $tm = moment_from_excel($date); is($tm->to_string, $string, "moment_from_excel($date)"); delta_ok(moment_to_excel($tm), $date, "moment_to_excel($tm)"); } done_testing(); eval { require DateTime::Format::Excel; require Benchmark; { print "\nComparing DateTime::Format::Excel and Time::Moment\n"; my $date = 30188.010650613425; Benchmark::cmpthese( -10, { 'DateTime' => sub { my $dt = DateTime::Format::Excel->parse_datetime($date); }, 'Time::Moment' => sub { my $tm = moment_from_excel($date); }, }); } };