DateTime::Format::Excel(3pm)

1DateTime::Format::ExcelU(s3e)r Contributed Perl DocumentaDtaitoenTime::Format::Excel(3)
2
3
4

NAME

6       DateTime::Format::Excel - Convert between DateTime and Excel dates.
7

SYNOPSIS

9           use DateTime::Format::Excel;
10
11           # From Excel via class method:
12
13           my $datetime = DateTime::Format::Excel->parse_datetime( 37680 );
14           print $datetime->ymd();     # prints 2003-02-28
15
16           my $datetime = DateTime::Format::Excel->parse_datetime( 40123.625 );
17           print $datetime->iso8601(); # prints 2009-11-06T15:00:00
18
19           #  or via an object
20
21           my $excel = DateTime::Format::Excel->new();
22           print $excel->parse_datetime( 25569 )->ymd; # prints 1970-01-01
23
24           # Back to Excel number:
25
26           use DateTime;
27           my $dt = DateTime->new( year => 1979, month => 7, day => 16 );
28           my $daynum = DateTime::Format::Excel->format_datetime( $dt );
29           print $daynum; # prints 29052
30
31           my $dt_with_time = DateTime->new( year => 2010, month => 7, day => 23
32                                           , hour => 18, minute => 20 );
33           my $excel_date = DateTime::Format::Excel->format_datetime( $dt_with_time );
34           print $excel_date; # prints 40382.763888889
35
36           # or via the object created above
37           my $other_daynum = $excel->format_datetime( $dt );
38           print $other_daynum; # prints 29052
39

DESCRIPTION

41       Excel uses a different system for its dates than most Unix programs.
42       This module allows you to convert between a few of the Excel raw
43       formats and "DateTime" objects, which can then be further converted via
44       any of the other "DateTime::Format::*" modules, or just with
45       "DateTime"'s methods.
46
47       If you happen to be dealing with dates between 1 Jan 1900 and
48       1 Mar 1900 please read the notes on epochs.
49
50       Since version 0.30 this modules handles the time part (the decimal
51       fraction of the Excel time number) correctly, so you can convert a
52       single point in time to and from Excel format. (Older versions did only
53       calculate the day number, effectively loosing the time of day
54       information).  The H:M:S is stored as a fraction where 1 second = 1 /
55       (60*60*24).
56
57       If you're wanting to handle actual spreadsheet files, you may find
58       Spreadsheet::WriteExcel and Spreadsheet::ParseExcel of use.
59

CONSTRUCTORS

61   new
62       Creates a new "DateTime::Format::Excel" instance. This is generally not
63       required for simple operations. If you wish to use a different epoch,
64       however, then you'll need to create an object.
65
66          my $excel = DateTime::Format::Excel->new()
67          my $copy = $excel->new();
68
69       It takes no parameters. If called on an existing object then it clones
70       the object.
71
72   clone
73       This method is provided For those who prefer to explicitly clone via a
74       method called "clone()". If called as a class method it will die.
75
76          my $clone = $original->clone();
77

CLASS/OBJECT METHODS

79       These methods work as both class and object methods.
80
81   parse_datetime
82       Given an Excel day number, return a "DateTime" object representing that
83       date and time.
84
85           # As a class method
86           my $datetime = DateTime::format::Excel->parse_datetime( 37680 );
87           print $datetime->ymd('.'); # '2003.02.28'
88
89           # Or via an object
90           my $excel = DateTime::Format::Excel->new();
91           my $viaobj $excel->parse_datetime( 25569 );
92           print $viaobj->ymd; # '1970-01-01'
93
94   format_datetime
95       Given a "DateTime" object, return the Excel daynum time.
96
97           use DateTime;
98           my $dt = DateTime->new( year => 1979, month => 7, day => 16 );
99           my $daynum = DateTime::Format::Excel->format_datetime( $dt );
100           print $daynum; # 29052
101
102           # or via an object
103           my $excel = DateTime::Format::Excel->new();
104           $excel->epoch_mac(); # Let's imagine we want the Mac number
105           my $mac_daynum = $excel->format_datetime( $dt );
106           print $mac_daynum; # 27590
107

OBJECT METHODS

109   epoch
110       In scalar context, returns a string identifying the current epoch.
111
112          my $epoch = $excel->epoch();
113
114       Currently either `mac' or `win' with the default being `win'.
115
116       In list context, returns appropriate parameters with which to create a
117       "DateTime" object representing the start of the epoch.
118
119          my $base = DateTime->new( $excel->epoch );
120
121   epoch_mac
122       Set the object to use a Macintosh epoch.
123
124          $excel->epoch_mac(); # epoch is now  1 Jan 1904
125
126       Thus, 1 maps to "2 Jan 1904".
127
128   epoch_win
129       Set the object to use a Windows Excel epoch.
130
131          $excel->epoch_win(); # epoch is now 30 Dec 1899
132
133       Thus, 2 maps to "1 Jan 1900".
134

EPOCHS

136       Excel uses ``number of days since 31 Dec 1899''. Naturally, Microsoft
137       messed this up because they happened to believe that 1900 was a leap
138       year. In this module, we assume what Psion assumed for their Abacus /
139       Sheet program: 1 Jan 1900 maps to 2 rather than 1. Thus, 61 maps to
140       1 Mar 1900 in both Excel and this module (and Abacus).
141
142       Excel for Macintosh has a little option hidden away in its calculations
143       preferences. It can use either the Windows epoch, or it can use the
144       Macintosh epoch, which means that the day number is calculated as
145       ``number of days since  1 Jan 1904''. This module supports both
146       notations.
147
148       Note: the results of this module have only been compared with Microsoft
149       Excel for Macintosh 98 and Abacus on the Acorn Pocket Book. Where they
150       have differed, I've opted for Abacus's result rather than Excel's.
151

THANKS

153       Dave Rolsky (DROLSKY) for kickstarting the DateTime project.
154

SUPPORT

156       Support for this module is provided via the datetime@perl.org email
157       list. See http://lists.perl.org/ for more details.
158
159       Alternatively, log them via the CPAN RT system via the web or email:
160
161           http://rt.cpan.org/NoAuth/ReportBug.html?Queue=DateTime%3A%3AFormat%3A%3AExcel
162           bug-datetime-format-excel@rt.cpan.org
163
164       This makes it much easier for us to track things and thus means your
165       problem is less likely to be neglected.
166

LICENCE AND COPYRIGHT

168       Copyright © 2003-2010 Iain Truskett, Dave Rolsky, Achim Bursian.  All
169       rights reserved. This library is free software; you can redistribute it
170       and/or modify it under the same terms as Perl itself.
171
172       The full text of the licences can be found in the Artistic and COPYING
173       files included with this module.
174

AUTHOR

176       Originally written by Iain Truskett <spoon@cpan.org>, who died on
177       December 29, 2003.
178
179       Maintained by Dave Rolsky <autarch@urth.org> and, since 2010-06-01, by
180       Achim Bursian <aburs@cpan.org>.
181
182       The following people have either submitted patches or suggestions, or
183       their bug reports or comments have inspired the appropriate patches.
184
185        Peter (Stig) Edwards
186        Bobby Metz
187