#!/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);
            },
        });
    }  
};
