This lighting hours tool is provided free of charge.

This spreadsheet is designed to provide the number of lighting hours  – i.e. night-time hours – in a day, week or month, which will vary according to the season. With this data it is possible to assess the energy consumption used for exterior or street lighting.

It contains routines from the US National Oceanic and Atmospheric Administration, adapted for Excel by Greg Pelletier. Additional routines for Geolocation searching using Google Maps have been taken from Jamie Bull at oCoCarbon.

To use the tool simply enter a location in the search cell (if you have an internet connection – otherwise provide latitude and longitude data). You can define the lighting pattern and then define the precise  output that meets your needs. Sunrise and sunset times are provided and in order to determine these the  tool will take into account Time Zone and  Daylight Savings dates for many countries.

The workbook also contains a sheet describing how lighting data can be used to interpret energy consumption, which those new to energy management may find useful.

The current release is version 1.3.

Minor changes made for 64-bit Windows and Excel 2003 compatibility
Routines to handle perpetual day and night situations.

This is an Excel 2003 “.xls” file, which has been tested on Excel 2003, Excel 2010, Excel 2016 and Excel 2019. All Visual Basic code is accessible and suggestions for improvement are warmly welcome. In order to notify those who wish to receive update information you will be asked to provide your email address prior to uploading this file.

Please note that this file is provided “as is” and no warranty is made as to the accuracy of the output or as to any consequences arising from using the file. Please also be aware that while this spreadsheet can calculate lighting hours related to sunrise and sunset, additional lighting requirements that may arise from cloud or other weather phenomena are not estimated.


Download Lighting Tool


While you are here……

Why not check out other Excel tools from SustainSuccess.

Learn about our Services in Energy Efficiency, Sustainability and Cost Reduction.

Or read some articles and blogs about why Resource Efficiency is not Easy!

Or see my reviews of some great  books.



Print Friendly, PDF & Email