home | help 
DateTim...t::Excel(3)  User Contributed Perl Documentation DateTim...t::Excel(3)

NAME
     DateTime::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  Spread-
     sheet::WriteExcel and Spreadsheet::ParseExcel of use.

CONSTRUCTORS
   new
     Creates a new "DateTime::Format::Excel" instance. This is generally not re-
     quired  for  simple  operations. If you wish to use a different epoch, how-
     ever, 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  pro-
     gram:  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 Macin-
     tosh 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 Ex-
     cel  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 prob-
     lem 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.32.1			   2023-02-20		   DateTim...t::Excel(3)
home | help