++ed by:

1 non-PAUSE user.

Iain Truskett (PAUSE Custodial Account)


DateTime::Format::Excel - convert between DateTime and Excel dates.


    use DateTime::Format::Excel;

    # From Excel via 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();
    print $excel->parse_datetime( 25569 )->ymd; # "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; # 29052

    # or via an object
    my $other_daynum = $excel->format_datetime( $dt );
    print $other_daynum; # 29052


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 Unix epoch times.

If you happen to be dealing with dates between 1 Jan 1900 and 1 Mar 1900 please read the notes on epochs.



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.


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();


These methods work on either our objects or as class methods.


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"


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



In scalar context, returns a string identifying the current epoch.

   my $epoch = $excel->epoch();

Currently either 'mac' or '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 );


Set the object to use a Macintosh epoch.

   $excel->epoch_mac(); # epoch is now  1 Jan 1904

Thus, 1 maps to 2 Jan 1904.


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.


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.


Dave Rolsky (DROLSKY) for kickstarting the DateTime project.


Please report any requests, suggestions or bugs via the system at http://rt.cpan.org/, or email <bug-datetime-format-excel@rt.cpan.org>. This makes it much easier for me to track things and thus means your problem is less likely to be neglected.


Copyright © Iain Truskett, 2003. 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 licenses can be found in the Artistic and COPYING files included with this module.


Easy conversion between Excel's day number date system and the DateTime object.


Iain Truskett <spoon@cpan.org>


datetime@perl.org mailing list.


perl, DateTime