Site Search:

Stand-Alone Date/Time Stamps in Microsoft Excel

FacebooktwitterredditlinkedinmailFacebooktwitterredditlinkedinmail

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.

Additional Reading:

New Stand-alone Data Logging Features Added to the WinDaq Dashboard

 

Follow Us
FacebooktwitterlinkedinyoutubeFacebooktwitterlinkedinyoutube

 Categories: Data Acquisition, Data Logger, Excel

 Tags: ,

 Bookmark the permalink

 RSS Feed (comments for this post)

 Post a comment

 Trackback URL

One Comment

  1. Avatar
    Chen
    Posted October 12, 2021 at 9:57 am Permalink

    Is the starting date 01/01/1900 or 12/30/1899?

    Here are some helpful info about this date/time number.

    https://www.myonlinetraininghub.com/excel-date-and-time

    https://help.libreoffice.org/3.3/Calc/Date_and_Time_Functions

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>