1DateTime::Format::ExcelU(s3e)r Contributed Perl DocumentaDtaitoenTime::Format::Excel(3)
2
3
4
6 DateTime::Format::Excel - Convert between DateTime and Excel dates.
7
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
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
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
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
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
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
153 Dave Rolsky (DROLSKY) for kickstarting the DateTime project.
154
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
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
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
189 datetime@perl.org mailing list.
190
191 http://datetime.perl.org/
192
193 perl, DateTime, Spreadsheet::WriteExcel
194
195
196
197perl v5.30.1 2020-01-29 DateTime::Format::Excel(3)