1#!/usr/bin/perl
2use strict;
3use warnings;
4
5# Convert between Excel serial dates and Time::Moment
6use Time::Moment;
7
8use constant EXCEL_EPOCH => Time::Moment->from_string('1899-12-30T00Z')->rd;
9
10sub moment_from_excel {
11    @_ == 1 or die q/Usage: moment_from_excel(date)/;
12    my ($date) = @_;
13    return Time::Moment->from_rd($date + ($date < 61), epoch => EXCEL_EPOCH);
14}
15
16sub moment_to_excel {
17    @_ == 1 or die q/Usage: moment_to_excel(moment)/;
18    my ($moment) = @_;
19    my $date = $moment->rd - EXCEL_EPOCH;
20    return $date - ($date < 61);
21}
22
23my @tests = (
24    [ '1899-12-31T00:00:00Z',     0                  ],
25    [ '1900-01-01T00:00:00Z',     1                  ],
26    [ '1900-02-27T00:00:00Z',     58                 ],
27    [ '1900-02-28T00:00:00Z',     59                 ],
28    [ '1900-03-01T00:00:00Z',     61                 ],
29    [ '1900-03-02T00:00:00Z',     62                 ],
30    [ '1969-12-31T23:59:58Z',     25568.9999768519   ],
31    [ '1969-12-31T23:59:58.500Z', 25568.9999826389   ],
32    [ '1969-12-31T23:59:58.800Z', 25568.9999861111   ],
33    [ '1969-12-31T23:59:58.900Z', 25568.9999872685   ],
34    [ '1969-12-31T23:59:58.980Z', 25568.9999881944   ],
35    [ '1969-12-31T23:59:58.990Z', 25568.9999883102   ],
36    [ '1969-12-31T23:59:58.998Z', 25568.9999884028   ],
37    [ '1969-12-31T23:59:58.999Z', 25568.9999884143   ],
38    [ '1969-12-31T23:59:59Z',     25568.9999884259   ],
39    [ '1969-12-31T23:59:59.001Z', 25568.9999884375   ],
40    [ '1969-12-31T23:59:59.002Z', 25568.9999884490   ],
41    [ '1969-12-31T23:59:59.010Z', 25568.9999885417   ],
42    [ '1969-12-31T23:59:59.020Z', 25568.9999886574   ],
43    [ '1969-12-31T23:59:59.100Z', 25568.9999895834   ],
44    [ '1969-12-31T23:59:59.200Z', 25568.9999907408   ],
45    [ '1969-12-31T23:59:59.300Z', 25568.9999918982   ],
46    [ '1969-12-31T23:59:59.400Z', 25568.9999930556   ],
47    [ '1969-12-31T23:59:59.490Z', 25568.9999940973   ],
48    [ '1969-12-31T23:59:59.499Z', 25568.9999942014   ],
49    [ '1969-12-31T23:59:59.500Z', 25568.9999942130   ],
50    [ '1969-12-31T23:59:59.501Z', 25568.9999942245   ],
51    [ '1969-12-31T23:59:59.510Z', 25568.9999943287   ],
52    [ '1969-12-31T23:59:59.600Z', 25568.9999953704   ],
53    [ '1969-12-31T23:59:59.700Z', 25568.9999965278   ],
54    [ '1969-12-31T23:59:59.800Z', 25568.9999976852   ],
55    [ '1969-12-31T23:59:59.900Z', 25568.9999988426   ],
56    [ '1969-12-31T23:59:59.980Z', 25568.9999997686   ],
57    [ '1969-12-31T23:59:59.990Z', 25568.9999998843   ],
58    [ '1969-12-31T23:59:59.998Z', 25568.9999999768   ],
59    [ '1969-12-31T23:59:59.999Z', 25568.9999999885   ],
60    [ '1970-01-01T00:00:00Z',     25569              ],
61    [ '1970-01-01T00:00:00.001Z', 25569.0000000115   ],
62    [ '1970-01-01T00:00:00.002Z', 25569.0000000232   ],
63    [ '1970-01-01T00:00:00.010Z', 25569.0000001157   ],
64    [ '1970-01-01T00:00:00.020Z', 25569.0000002314   ],
65    [ '1970-01-01T00:00:00.100Z', 25569.0000011574   ],
66    [ '1970-01-01T00:00:00.200Z', 25569.0000023148   ],
67    [ '1970-01-01T00:00:00.300Z', 25569.0000034722   ],
68    [ '1970-01-01T00:00:00.400Z', 25569.0000046296   ],
69    [ '1970-01-01T00:00:00.490Z', 25569.0000056713   ],
70    [ '1970-01-01T00:00:00.499Z', 25569.0000057755   ],
71    [ '1970-01-01T00:00:00.500Z', 25569.0000057870   ],
72    [ '1970-01-01T00:00:00.501Z', 25569.0000057986   ],
73    [ '1970-01-01T00:00:00.510Z', 25569.0000059027   ],
74    [ '1970-01-01T00:00:00.600Z', 25569.0000069444   ],
75    [ '1970-01-01T00:00:00.700Z', 25569.0000081018   ],
76    [ '1970-01-01T00:00:00.800Z', 25569.0000092592   ],
77    [ '1970-01-01T00:00:00.900Z', 25569.0000104166   ],
78    [ '1970-01-01T00:00:00.980Z', 25569.0000113426   ],
79    [ '1970-01-01T00:00:00.990Z', 25569.0000114583   ],
80    [ '1970-01-01T00:00:00.998Z', 25569.0000115510   ],
81    [ '1970-01-01T00:00:00.999Z', 25569.0000115625   ],
82    [ '1970-01-01T00:00:01Z',     25569.0000115741   ],
83    [ '1970-01-01T00:00:01.001Z', 25569.0000115857   ],
84    [ '1970-01-01T00:00:01.002Z', 25569.0000115972   ],
85    [ '1970-01-01T00:00:01.010Z', 25569.0000116898   ],
86    [ '1970-01-01T00:00:01.020Z', 25569.0000118056   ],
87    [ '1970-01-01T00:00:01.100Z', 25569.0000127315   ],
88    [ '1970-01-01T00:00:01.200Z', 25569.0000138889   ],
89    [ '9999-12-31T23:59:58Z',     2958465.9999768520 ],
90    [ '9999-12-31T23:59:58.500Z', 2958465.9999826388 ],
91    [ '9999-12-31T23:59:58.800Z', 2958465.9999861112 ],
92    [ '9999-12-31T23:59:58.900Z', 2958465.9999872684 ],
93    [ '9999-12-31T23:59:58.980Z', 2958465.9999881946 ],
94    [ '9999-12-31T23:59:58.990Z', 2958465.9999883100 ],
95    [ '9999-12-31T23:59:58.998Z', 2958465.9999884027 ],
96    [ '9999-12-31T23:59:58.999Z', 2958465.9999884143 ],
97    [ '9999-12-31T23:59:59Z',     2958465.9999884260 ],
98    [ '9999-12-31T23:59:59.001Z', 2958465.9999884376 ],
99    [ '9999-12-31T23:59:59.002Z', 2958465.9999884493 ],
100    [ '9999-12-31T23:59:59.010Z', 2958465.9999885415 ],
101    [ '9999-12-31T23:59:59.020Z', 2958465.9999886574 ],
102    [ '9999-12-31T23:59:59.100Z', 2958465.9999895832 ],
103    [ '9999-12-31T23:59:59.200Z', 2958465.9999907408 ],
104    [ '9999-12-31T23:59:59.300Z', 2958465.9999918980 ],
105    [ '9999-12-31T23:59:59.400Z', 2958465.9999930556 ],
106    [ '9999-12-31T23:59:59.490Z', 2958465.9999940973 ],
107    [ '9999-12-31T23:59:59.499Z', 2958465.9999942016 ],
108    [ '9999-12-31T23:59:59.500Z', 2958465.9999942128 ],
109    [ '9999-12-31T23:59:59.501Z', 2958465.9999942244 ],
110    [ '9999-12-31T23:59:59.510Z', 2958465.9999943287 ],
111    [ '9999-12-31T23:59:59.600Z', 2958465.9999953704 ],
112    [ '9999-12-31T23:59:59.700Z', 2958465.9999965276 ],
113    [ '9999-12-31T23:59:59.800Z', 2958465.9999976852 ],
114    [ '9999-12-31T23:59:59.900Z', 2958465.9999988424 ],
115    [ '9999-12-31T23:59:59.980Z', 2958465.9999997686 ],
116    [ '9999-12-31T23:59:59.990Z', 2958465.9999998841 ],
117    [ '9999-12-31T23:59:59.998Z', 2958465.9999999767 ],
118    [ '9999-12-31T23:59:59.999Z', 2958465.9999999884 ],
119);
120
121use Test::More          0.88;
122use Test::Number::Delta 1.06 relative => 1E-10;
123
124foreach my $test (@tests) {
125    my ($string, $date) = @$test;
126    my $tm = moment_from_excel($date);
127    is($tm->to_string, $string, "moment_from_excel($date)");
128    delta_ok(moment_to_excel($tm), $date, "moment_to_excel($tm)");
129}
130
131done_testing();
132
133eval {
134    require DateTime::Format::Excel;
135    require Benchmark;
136    {
137        print "\nComparing DateTime::Format::Excel and Time::Moment\n";
138        my $date = 30188.010650613425;
139        Benchmark::cmpthese( -10, {
140            'DateTime' => sub {
141                my $dt = DateTime::Format::Excel->parse_datetime($date);
142            },
143            'Time::Moment' => sub {
144                my $tm = moment_from_excel($date);
145            },
146        });
147    }
148};
149