The parse_datetime routine in DateTime::Format::Excel is 100% when
converting dates from the Excel serial date format, but I found it
doesn't convert the time portion of a value, i.e. the decimal portion of
the float. For example, the following code:
my $datetime = DateTime::Format::Excel->parse_datetime(39105.429861111);
print $datetime->strftime("%m/%d/%Y %H:%M:%S");
produces "01/23/2007 00:00:00" instead of the real date/time value of
"01/23/2007 10:19:00". It appears that the DateTime add() routine
treats the float as an integer, thus cutting off the Excel time value,
which you wouldn't expect add() to understand anyway. To address this,
I re-wrote the parse_datetime routine to handle the time values. I
thought I'd share this with the community in case anyone else has needed
this.
sub parse_datetime
{
my $self = shift;
croak 'No date specified.' unless @_;
croak 'Invalid number of days' unless $_[0] =~ /^ (\d+ (?: (\.\d+)
)? ) $/x;
my $days = 0;
my $secs = 0;
$days = int($1) if defined $1; # int() not
really needed, but it's clean
if (defined $2)
{
$secs = $2;
$secs *= 86400; # Convert
decimal value to whole seconds
$secs = int($secs + .5); # Round the
seconds like Excel does
}
my $dt = DateTime->new( $self->epoch );
$dt->add( days => $days, seconds => $secs );
return $dt;
}
Bobby
