use strict;
use warnings;

use Date::Manip;
Date_Init( "TZ=CST6CDT" ); # see Date::Manip docs about this

my $datestring = 'Dec 1, 2005 11:17:20 PM';

my ( $date_sn, $time_sn ) = calc_serial_numbers( $datestring );

print "date SN = $date_sn\n"; # 38687
print "time SN = $time_sn\n"; # 0.97037037

sub calc_serial_numbers
{
    # This sub takes a string containing a date and time, and
    # calculates the corresponding Microsoft date and time serial
    # numbers.  Note there is a bug in the MS calculation (for
    # historical reasons MS calculates 1900 as a leap year; source
    # is Date::Calc docs) which necessitates adding an extra day to
    # the date serial number (therefore it is calculated based on
    # 12-31-1899 rather than 1-1-1900).  These serial numbers are
    # suitable for storing in MS Access as a date/time field, and
    # can also be used in MS Excel.

    my ( $datetimestring ) = @_;

    # calculate the number of days since 12-31-1899
    # (or 1-1-1900, according to MS)

    my $parsed_date = ParseDate( $datetimestring );
    my $date_delta = DateCalc( 'Dec 31, 1899', $parsed_date );
    my $date_sn = Delta_Format( $date_delta, 0, ( '%dh' ) ) + 1;

    # calculate the fraction of the current day, in seconds
    # 60*60*24 = 86400 seconds per day

    my $daystart = Date_SetTime( $datetimestring, '00:00:00' );
    my $time_delta = DateCalc( $daystart, $parsed_date );
    my $num_sec = Delta_Format( $time_delta, 0, ( '%sh' ) );
    my $time_sn = sprintf( "%.8f", $num_sec/86400 );

    return( $date_sn, $time_sn );
}