Stand-Alone Date/Time Stamps in Microsoft Excel

A critical component of data acquisition, date/time stamps allow you to determine the status of your device under test at a particular instant in time. How that date/time stamp is displayed, and the precision (to the nearest tenth or hundredth of a second) can be quite subjective. Some like to see the date first (mm/dd/yyyy), followed by the time (hh:mm:ss), other prefer to see just the time, to the fraction of a second.

How to interpret a floating real number date/time stamp

When recording CSV data in stand-alone mode using a contemporary DATAQ data logger in, the date/time stamp might look a little peculiar. That’s because dates and times are stored as floating real numbers, and look something like this:

44369.7038194444

The time portion of the date/time stamp is represented as a decimal fraction, where 24 hours = 1 full day, an hour is 1/24 of a day (0.04167), a minute is 1/1,440 of a day (0.000694) and a second is 1/86,400 of a day (0.00001157).

So 44369.7038194444 equates to 6/22/21 (44,369 days from 01/01/1900) and .7038194444 equates to 4:53PM (24 x 0.703819444 or 16:53:00).

Reformatting the date/time stamp

While the floating-point number format isn’t pretty or easy to understand at first glance, it does offer a high degree of accuracy when it comes to the date/time stamp. Fortunately, features in Microsoft Excel make it relatively easy to covert these date/time stamps into something more meaningful.

Simply right-click on the date/time column (column A) and choose ‘Format Cells’ (as shown below).

Here you can choose the ‘Date’ or ‘Time’ category and select a format type that’s more to your liking.

Choosing the ‘Custom’ category allows for even greater customization, as shown below.

With the ‘Custom’ category chosen, and because of the precision allowed using floating real numbers, you can display time to the thousandth of a second*.

Simply add .000 to the end of custom format type, as shown below.

The floating real number format, generated when recording CSV data in stand-alone mode, allows for high accuracy date/time stamp information. And while the format may not be easy to look at or to interpret at first glance, tools in Microsoft Excel make it easy to turn this information into a meaningful and precise date/time stamps.

* The resolution of the on-board real-time-clock is 1 second, with a tolerance of 50ppm. This determines the accuracy of the start time of the CVS file. The ADC pacing clock has a tolerance of 100ppm, and determines the accuracy of the interval between samples.