Code:
$
$ cat data.txt
GMT excel datetime Value
42927.46024305555 2,223,003,680
42927.46030092593 2,223,323,176
42927.46035879629 2,225,181,648
42927.46041666667 2,225,843,160
42927.46047453703 2,227,239,952
42927.46053240741 2,227,508,248
42927.46059027778 2,229,306,008
42927.460648148146 2,229,592,736
42927.46070601852 2,230,992,288
42927.460763888885 2,231,824,880
42927.46082175926 2,233,473,560
42927.46087962963 2,233,610,200
42927.4609375 2,234,898,400
$
$ perl -lne 'BEGIN {
# Use core module Time::Piece to set up the epoch. I am unable
# to use dates less than "12/17/1902" as the epoch due to an
# error in the module. Microsoft Excel uses "1/1/1900" as its
# epoch. So I convert using "1/1/1903" as epoch and then subtract
# $delta days from the result.
use Time::Piece;
$epoch = Time::Piece->strptime("1-1-1903","%m-%d-%Y");
$delta = 365 * 3 + 2
}
if ($. == 1) {
# From the header line, get the width of the first column.
$len = index($_, "Value");
$fmt = "%-${len}s%s\n";
print;
next
}
# Parse the data to retrieve the MS Excel date and the value.
# Perform date arithmetic. You can only add seconds to a Time::Piece
# object using "+" operator. So convert days to seconds by multiplying
# with 24*60*60.
($dt, $val) = m/^(\S+)\s+(\S+)$/;
$fmt_dt = $epoch + ($dt - $delta)*24*60*60;
printf($fmt, $fmt_dt->strftime("%m/%d/%Y %H:%M:%S"), $val);
' data.txt
GMT excel datetime Value
07/11/2017 11:02:45 2,223,003,680
07/11/2017 11:02:50 2,223,323,176
07/11/2017 11:02:54 2,225,181,648
07/11/2017 11:03:00 2,225,843,160
07/11/2017 11:03:04 2,227,239,952
07/11/2017 11:03:10 2,227,508,248
07/11/2017 11:03:15 2,229,306,008
07/11/2017 11:03:19 2,229,592,736
07/11/2017 11:03:25 2,230,992,288
07/11/2017 11:03:29 2,231,824,880
07/11/2017 11:03:35 2,233,473,560
07/11/2017 11:03:40 2,233,610,200
07/11/2017 11:03:45 2,234,898,400
$
$
Convert Numeric Time to Readable Timestamp - Perl