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