Setting Time Axis Values in Excel

If you are involved in the commissioning or retrocommissioning business, it is quite likely that you spend a lot of time working with trend data from data loggers and control systems.  One of the questions I am asked frequently with regard to looking at trend data is:

How do you adjust the time axis on a time series graph to a specific date and time?

Having just been asked that question again, I thought it would make a good topic for a blog post since it is a really good question and, at least for me, a big mystery until I figured out how Excel works with dates and times.

An Example of Why This Matters

Since spreadsheets are just big mathematical workbooks, it would be really handy if you could add and subtract dates, compare dates, divide a date range into equal increments, etc.   For instance, say I had a string of trend data from an AHU that included the flow rate and the temperature rise across a preheat coil.  By adding a column to the spreadsheet, I could calculate the instantaneous load on the coil using the sensible heat equation:

image

(Incidentally, you can find this equation and other handy HVAC equations in a slide set linked off of the right side of my blog home page.)

image

Here is an example where I have done just that.

image

The calculation gives me the instantaneous load in Btu/hr. Meaning that if that condition lasted one hour (i.e. the sample rate was once an hour), I might assume that the system used that many Btus.  If I made that assumption, I could figure out how many Btus were used for the entire period of time I was sampling data by adding up the values in the column that has my calculation in it.

Assuming an HVAC system is steady-state for an hour may not be a good assumption.  For example it is not unusual to find control loops hunting at 5 to 10 cycles per hour.  For that reason, I usually set my sampling rate much faster than once an hour to allow me to capture potential control loop instability and other non-steady state conditions. If I then wanted to calculate how many Btus were used based on the metrics I collected in my data set, I would have to adjust the value calculated by the sensible heat equation to reflect the sample interval. 

For instance,  if I was collecting data at the rate of one sampler per minute, then the Btu’s consumed during the interval will be 1/60th of the value I would calculate using the sensible heat equation since the equation yields results with units of Btu/hr but the interval under consideration is only 1 minute or 1/60th of an hour.

The bottom line is you need to divide the instantaneous rate of consumption calculated by the sensible heat equation using the logger data (which yields Btu’s per hour) by the time period during which that rate of consumption occurred. 

The time period is the interval between data samples.  Since the sampling rate I used in the example above was once a minute, I could just divide the number in the Btu/hr. column by 60 (the number of minutes in an hour) and call it good.  

But sometimes, the data logging or trending system “hiccups”;  meaning something happens that causes it to miss a sample or series of samples.   As a result, a technique that assumes the sampling rate was consistent through the entire data set could mislead you.

For instance, say something caused the data collection system to miss capturing data for 15 minutes as illustrated below.

image

You could solve that problem by not assuming the sampling rate was steady and calculating it for each piece of data based on its time stamp relative to the previous one; i.e. by subtracting the later time stamp from the earlier one, as illustrated below.

image

How It Works

To make it possible to directly subtract two different dates and times (or otherwise manipulate them mathematically), Excel represents a date as a number that is referenced to 1 on January 1, 1900 and incremented by 1 each day thereafter.  The appearance of this number as a date is simply a formatting option that is applied to the cell containing it.  Meaning that if I format a cell to show a date and time and then enter a date and time, it looks just like the date and time format I have applied.

image

But, if I reformat that same cell to show its contents as a number, I get a cell with a really large number with a  lot of decimal places.

image

In the example above, I entered the date and time that I was writing this blog post into cell A1.  If you do the math, you will discover that the day I am writing this (January 25, 2015) is 42,029 days after January 1, 1900.  And, you will discover that 6:25 PM (18 hours and 25 minutes or 1,105 minutes into the day) is 0.76736111 of a day (to eight decimal places).

Thus, the number Excel uses to represent the point in time that I am writing this is 42,029.76736111.   Tomorrow at midnight (1/26/2015. 00:00 AM), the number will be 42,030.00000000.

Scaling a Time Axis in an Excel Chart

The time axis in an Excel chart is working with a number just like what I described in the preceding discussion.  That means that to scale it, you need to be working with it as a number rather than a date. 

So, lets look at how you would go about scaling the “X” axis in an Excel chart if it is a date/time axis.  I will use a chart I created recently for an air handling system I was working with to illustrate this.  Note in the image of the chart below that it is for an interval of time that started at 4:00 pm on December 23, 2014 and ended at 11:00 am on December 24, 2014 .  

To get started on the formatting process, you hover your cursor over the X axis and right click, which should give you the option to format the axis.

image

When you pick that, you get a dialog box and if you select “Axis Options” (red arrow) you will notice that there are some really big numbers for the “Maximum” (green arrow) and “Minimum” (blue arrow). 

image

In the context of the graph we are looking at for this example, if you do the math, you will discover that December 23rd was the 41,996th day since January 1, 1900. 

Since the number is incremented by 1 each day, 1 hour is 1/24 or .0416667 to seven decimal places.   So that means that  4:00 PM (the 16th hour of the day) would be 16 times (1/24) or .6666667 to seven decimal places.  So to set the minimum for December 23rd at 4:00 PM, which is where I set it in the example graph, I made the minimum value for the axis 41,996.6666667 (41,996 for the day plus .6666667 for the hour of the day). 

The maximum was set using the same concept.   Note that the maximum value always has to be bigger than the minimum value, otherwise Excel will not accept it. 

Meaning if you have created a graph for a really large trend file and have focused it on, say, a day early in the data set and then want to refocus it on a day later in the data set, you will need to set the maximum value first so that it reflects the end of the new day you want to look at.  Then you set can the minimum value to reflect the beginning of the day (or what ever interval you want to look at).

A Handy Tool

When I first discovered how this works, I made myself a little spreadsheet that does the math for me. 

clip_image006

If I enter the date and time in the white square (red arrow above), the number associated with that date and time shows up in the gray square immediately to the right.  The table below it contains the values for common increments that I use for the major and minor divisions when I scale the axis.  For instance, I used 3 hours as the major division on the graph you are looking at and 1 hour for the minor division.  So, in the “Axis Options” dialog box, I set the “Major  Unit” to .125 (purple arrows below) and the “Minor Unit” to .04166667 (orange arrows below).

image

When I am doing charts, I usually put a copy of my little tool in the Excel file I am working with to make it easier to use, meaning you would find a copy of it in the spreadsheet I am using as an example if you tab over to the right a bit.

image

There is a bit more about this in a couple of blog posts I did a while back along with a link to a copy of the spreadsheet tool if you are interested. 

The posts also discuss how to convert the text strings you get from some control systems and NOAA into a number Excel will recognize as a date and/or time …

Good News about NWS Weather Data, Plus Working with Date and Time in Excel

…. and how to shift a date and time to a different time zone.

Assessing Steam Consumption with an Alarm Clock: Step 1 – Working with the raw data and Excel time values

David-Signature1_thumb1_thumb

David Sellers
Senior Engineer – Facility Dynamics Engineering
PowerPoint-Generated-White_thumb1_th

Click here for a recent index to previous posts

This entry was posted in Excel Techniques. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s