DateTim...t::Excel(3)

📦
version 0.31 · 3 versions
, in epel 9

💖 DateTime::Format::Excel(3) — the manual page bestie 💖

okay so welcome to the DateTim...t::Excel(3) page from User Contributed Perl Documentation. We're gonna make Excel dates make sense together, no cap. 🫶

✨ NAME ✨

DateTime::Format::Excel — convert between DateTime and Excel dates. That's the whole vibe. 💅

✨ SYNOPSIS ✨

okay bestie, here's the quick-start code, copied exactly so we don't break anything:

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 🌸

okay so Excel dates are not like Unix dates — different system, different drama. This module converts between a few of the Excel raw formats and DateTime objects, and then you can pass those DateTime objects along to any of the other DateTime::Format::* modules, or just use DateTime's own methods. Periodt. ✨

If you happen to be dealing with dates between 1 Jan 1900 and 1 Mar 1900, pls lowkey read the notes on epochs before you spiral. 🌀

Since version 0.30 this module 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 only calculated the day number, effectively losing the time-of-day info (the original page says “loosing” — we love a typo 💀). 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. obsessed. 💖

🧸 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. Main character energy only when necessary. 💅

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. Versatile icons. 💅

🔥 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'

(Yes, bestie, that example really has DateTime::format::Excel with a lowercase f and my $viaobj $excel->parse_datetime( 25569 ); without an = — copy it exactly as shown, don't “fix” it.) 🙈

💫 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. Cute.

🧚 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. Iconic.

🌟 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). No cap.

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. Legend behavior. 👑

🫶 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. 💖

Copyright © 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 🔎

source/version vibes: perl v5.32.1, dated 2021-07-22, manual label DateTim...t::Excel(3). byeeee bestie 💅✨

📦 where this page comes from