DateTime::Format::ExceUser Contributed Perl DocumentDateTime::Format::Excel(3)NAMEDateTime::Format::Excel - Convert between DateTime and Excel dates.
SYNOPSIS
use DateTime::Format::Excel;
# From Excel via class method:
my $datetime = DateTime::Format::Excel->parse_datetime( 37680 );
print $datetime->ymd(); # prints 2003-02-28
my $datetime = DateTime::Format::Excel->parse_datetime( 40123.625 );
print $datetime->iso8601(); # prints 2009-11-06T15:00:00
# or via an object
my $excel = DateTime::Format::Excel->new();
print $excel->parse_datetime( 25569 )->ymd; # prints 1970-01-01
# Back to Excel number:
use DateTime;
my $dt = DateTime->new( year => 1979, month => 7, day => 16 );
my $daynum = DateTime::Format::Excel->format_datetime( $dt );
print $daynum; # prints 29052
my $dt_with_time = DateTime->new( year => 2010, month => 7, day => 23
, hour => 18, minute => 20 );
my $excel_date = DateTime::Format::Excel->format_datetime( $dt_with_time );
print $excel_date; # prints 40382.763888889
# or via the object created above
my $other_daynum = $excel->format_datetime( $dt );
print $other_daynum; # prints 29052
DESCRIPTION
Excel uses a different system for its dates than most Unix programs.
This module allows you to convert between a few of the Excel raw
formats and "DateTime" objects, which can then be further converted via
any of the other "DateTime::Format::*" modules, or just with
"DateTime"'s methods.
If you happen to be dealing with dates between 1 Jan 1900 and
1 Mar 1900 please read the notes on epochs.
Since version 0.30 this modules handles the time part (the decimal
fraction of the Excel time number) correctly, so you can convert a
single point in time to and from Excel format. (Older versions did only
calculate the day number, effectively loosing the time of day
information). The H:M:S is stored as a fraction where 1 second = 1 /
(60*60*24).
If you're wanting to handle actual spreadsheet files, you may find
Spreadsheet::WriteExcel and Spreadsheet::ParseExcel of use.
CONSTRUCTORS
new
Creates a new "DateTime::Format::Excel" instance. This is generally not
required for simple operations. If you wish to use a different epoch,
however, then you'll need to create an object.
my $excel = DateTime::Format::Excel->new()
my $copy = $excel->new();
It takes no parameters. If called on an existing object then it clones
the object.
clone
This method is provided For those who prefer to explicitly clone via a
method called "clone()". If called as a class method it will die.
my $clone = $original->clone();
CLASS/OBJECT METHODS
These methods work as both class and object methods.
parse_datetime
Given an Excel day number, return a "DateTime" object representing that
date and time.
# As a class method
my $datetime = DateTime::format::Excel->parse_datetime( 37680 );
print $datetime->ymd('.'); # '2003.02.28'
# Or via an object
my $excel = DateTime::Format::Excel->new();
my $viaobj $excel->parse_datetime( 25569 );
print $viaobj->ymd; # '1970-01-01'
format_datetime
Given a "DateTime" object, return the Excel daynum time.
use DateTime;
my $dt = DateTime->new( year => 1979, month => 7, day => 16 );
my $daynum = DateTime::Format::Excel->format_datetime( $dt );
print $daynum; # 29052
# or via an object
my $excel = DateTime::Format::Excel->new();
$excel->epoch_mac(); # Let's imagine we want the Mac number
my $mac_daynum = $excel->format_datetime( $dt );
print $mac_daynum; # 27590
OBJECT METHODS
epoch
In scalar context, returns a string identifying the current epoch.
my $epoch = $excel->epoch();
Currently either `mac' or `win' with the default being `win'.
In list context, returns appropriate parameters with which to create a
"DateTime" object representing the start of the epoch.
my $base = DateTime->new( $excel->epoch );
epoch_mac
Set the object to use a Macintosh epoch.
$excel->epoch_mac(); # epoch is now 1 Jan 1904
Thus, 1 maps to "2 Jan 1904".
epoch_win
Set the object to use a Windows Excel epoch.
$excel->epoch_win(); # epoch is now 30 Dec 1899
Thus, 2 maps to "1 Jan 1900".
EPOCHS
Excel uses ``number of days since 31 Dec 1899''. Naturally, Microsoft
messed this up because they happened to believe that 1900 was a leap
year. In this module, we assume what Psion assumed for their Abacus /
Sheet program: 1 Jan 1900 maps to 2 rather than 1. Thus, 61 maps to
1 Mar 1900 in both Excel and this module (and Abacus).
Excel for Macintosh has a little option hidden away in its calculations
preferences. It can use either the Windows epoch, or it can use the
Macintosh epoch, which means that the day number is calculated as
``number of days since 1 Jan 1904''. This module supports both
notations.
Note: the results of this module have only been compared with Microsoft
Excel for Macintosh 98 and Abacus on the Acorn Pocket Book. Where they
have differed, I've opted for Abacus's result rather than Excel's.
THANKS
Dave Rolsky (DROLSKY) for kickstarting the DateTime project.
SUPPORT
Support for this module is provided via the datetime@perl.org email
list. See http://lists.perl.org/ for more details.
Alternatively, log them via the CPAN RT system via the web or email:
http://rt.cpan.org/NoAuth/ReportBug.html?Queue=DateTime%3A%3AFormat%3A%3AExcel
bug-datetime-format-excel@rt.cpan.org
This makes it much easier for us to track things and thus means your
problem is less likely to be neglected.
LICENCE AND COPYRIGHT
Copyright X 2003-2010 Iain Truskett, Dave Rolsky, Achim Bursian. All
rights reserved. This library is free software; you can redistribute it
and/or modify it under the same terms as Perl itself.
The full text of the licences can be found in the Artistic and COPYING
files included with this module.
AUTHOR
Originally written by Iain Truskett <spoon@cpan.org>, who died on
December 29, 2003.
Maintained by Dave Rolsky <autarch@urth.org> and, since 2010-06-01, by
Achim Bursian <aburs@cpan.org>.
The following people have either submitted patches or suggestions, or
their bug reports or comments have inspired the appropriate patches.
Peter (Stig) Edwards
Bobby Metz
SEE ALSO
datetime@perl.org mailing list.
http://datetime.perl.org/
perl, DateTime, Spreadsheet::WriteExcel
perl v5.14.1 2011-07-21 DateTime::Format::Excel(3)
