Working with NetBackup CTIME Timestamps in Excel

NetBackup makes extensive use of timestamps for backup job start and stop times, expiration dates, etc.  NetBackup appends a timestamp to the client name to create a backup id (myserver_1303936891).  Don’t know about you, but I don’t natively understand CTIME timestamps.
To convert between the timestamp and “human readable” time, Symantec provides the bpdbm command with the -ctime flag to convert from NetBackup’s timestamp to the current time zone.  For example:

bash-3.00# bpdbm -ctime 1303936891
1303936891 = Wed Apr 27 15:41:31 2011

Some of my favorite NetBackup reports output job start/stop times and other data using CTIME timestamps:

• bpmedialist shows the expiration date for tapes
• bpimagelist shows the start and stop times for backup images in the catalog, as well as image expiration dates

It’s just not feasible to run bpdbm on every timestamp for output that may contain 10K lines.  Excel makes a far better tool.
To convert from CTIME to “Excel” time you need the following information:

1. The NetBackup timestamp (the number of seconds since 1/1/1970, in GMT)
2. The number of seconds per day (86,400)
3. The Excel serial number for 1/1/1970 (25,569)
4. Your current timezone offset in hours (for US/Central this is currently -5)

Excel date/time stamps are real numbers where the integer portion represents the number of days since 1/1/1900 (where 1/1/1900 is day 1).  The decimal portion of an Excel date/time value represents the portion of a single day (ie, 0.5 is 12 hours).  Therefore, to convert from CTIME to Excel time use the following formula:

timestamp/86400+25569+(-5/24)

Working with NetBackup CTIME Timestamps in Excel