Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages


home | help
DateTime::Format::ExceUser Contributed Perl DocumentDateTime::Format::Excel(3)

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

       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 /

       If you're wanting to handle actual spreadsheet files, you may find
       Spreadsheet::WriteExcel and Spreadsheet::ParseExcel of use.

       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.

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

       These methods work as both class	and object 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' 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 );

       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

       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.

       Support for this	module is provided via the email
       list. See	for more details.

       Alternatively, log them via the CPAN RT system via the web or email:

       This makes it much easier for us	to track things	and thus means your
       problem is less likely to be neglected.

       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.

       Originally written by Iain Truskett <>, who died on
       December	29, 2003.

       Maintained by Dave Rolsky <> and, since 2010-06-01, by
       Achim Bursian <>.

       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 mailing list.

       perl, DateTime, Spreadsheet::WriteExcel

perl v5.32.1			  2021-08-27	    DateTime::Format::Excel(3)


Want to link to this manual page? Use this URL:

home | help