Hourly Temperature Data – Where to Get It and How to Use It in Excel

Hourly temperature data is a historical record of temperatures with a temperature value every hour (00:00, 01:00, 02:00 etc.), for every day in the period covered, with each value representing the temperature at that specific moment in time.

You can download hourly temperature data into a spreadsheet via our Pro website. Or, if you are a programmer, you can get your software fetching hourly temperature data automatically from our API.

• What is hourly temperature data used for?
• Hourly temperature data vs. degree days
• Hourly temperature data from Degree Days.net and some calculations you can do with it in Excel or Google Sheets:
  • Daily average/maximum/minimum temperatures
  • Counting hours above or below a threshold
  • Bin data: counting hours in specified temperature bands

What is hourly temperature data used for?

People use hourly temperature data in many different ways, but here are a few common applications:

• HVAC system design and modelling, for which it can be useful to know how often and how long the outside air temperature is within certain temperature bands. Historical hourly temperatures can be especially useful when modelling systems that behave differently in different weather conditions, such as systems with multiple units that run in different configurations as the weather changes.
• Heat-pump specification and data analysis – heat-pump performance varies with the outdoor air temperature, so it can be useful to consider how much time the temperature spends in different temperature bands with different heat-pump operating characteristics.
• For the above applications and others, hourly temperature data is often used as the starting point for calculating data like hours above or below chosen temperature thresholds, and bin data. We have some examples further below that explain how to do these kinds of calculations in Excel using hourly temperature data from our Pro website.
• Building-simulation software often uses hourly temperature data as part of a sophisticated model of a building and its energy usage.

There are plenty of other uses too. However, for analysis of heating/cooling energy consumption, it is important to consider whether degree days could work better:

Back to top

Hourly temperature data vs. degree days

Hourly temperature data is important for certain sorts of specialist applications, but for analysis of heating/cooling energy consumption it's often better to use degree days. Degree days that are calculated accurately (in the way we calculate them) capture the full detail of the relevant temperature variations recorded throughout each day.

With well-chosen building base temperatures, heating/cooling degree days correlate directly with heating/cooling energy consumption (more degree days meaning more energy consumption) in a way that hourly temperatures do not, and for much analysis this makes degree days a lot easier to work with, without compromising on accuracy.

People sometimes assume that, if they have energy-usage data that is recorded hourly or more frequently, they should correlate it with hourly temperatures. This isn't really the case though, because on shorter timescales the relationship between outside air temperature and inside heating/cooling energy consumption is complex and laggy. Analysis of daily or weekly energy-usage totals with daily or weekly degree days will typically give much better results. This is explained further in this FAQ.

Back to top

Hourly temperature data from Degree Days.net

Here are a few rows of a spreadsheet showing the format of hourly temperature data downloaded from our Pro website (with the Pro Solo or Pro Team plan):

This example has Celsius temperatures, but you can get Fahrenheit temperatures instead if you prefer.

If you are a software developer, you can get hourly temperature data with the same information from our API (with the API Standard, Plus, or Premium plan).

For the rest of this article we will explain some useful figures you can calculate from a spreadsheet of hourly temperature data in the format shown above. If you are using the API, you can calculate the same figures (and more) using code rather than a spreadsheet.

Back to top

Calculating daily average/maximum/minimum temperatures from hourly temperature data in Excel

First you'll need to create a column containing the dates of interest. We'll put it in column H. Type the first date in cell H2, and the second date below it in cell H3, then select the two dates, and copy/paste them down the column. This should make Excel give a list of consecutive dates.

Then, remembering that our data format has the hourly temperature values in column E, and the dates of those hourly temperature values in column C (each date repeated for every hour in the day), we use the AVERAGEIFS, MAXIFS, and MINIFS functions in columns I, J, and K, to get the daily average (mean) temperatures, the daily maximum temperatures, and the daily minimum temperatures:

We would start by putting the formulas above in cells I2, J2, and K2, then we would copy those cells down the column to see daily average, maximum, and minimum temperature values for every day we included in column H.

Back to top

Counting the hours for which the temperature was above or below a threshold in Excel

These are simple calculations using the COUNTIFS function in Excel or Google Sheets. Remembering that our data format has the hourly temperatures in column E, we can use the following to count the hours for which the temperature was greater than or equal to 24 degrees:

=COUNTIFS(E:E, ">=24")

And to count the hours for which the temperature was below 16 degrees, we can use:

=COUNTIFS(E:E, "<16")

Back to top

Bin data: counting the hours for which the temperature was in specified temperature bands in Excel

Bin data is simple too, using the COUNTIFS function in Excel or Google Sheets. Again, remembering that our data format has the hourly temperatures in column E, we can use formulas like the following:

Back to top