Spreadsheet for Annual Calendar of Dark Sky Hours and Lunar Observing Hours

fisherka@csolutions.net Orig. 12/7/2006 Rev. 12/1/2007
Go to: Dark Sky Calendar Spreadsheet (Excel, 20Mb) | 2007 Dark Sky Hour Calendar N41 Lat | 2007 Lunar Observing Calendar N41 Lat | 2007 Lunar Colongitude (Facing) at 4 UTC | 2007 Local Sidereal Time at 4 UTC for W111.8 Long 41N Lat

A substantive code error was corrected on 12/1/2007. Users who downloaded the spreadsheet prior to 12/1/2007 will need to redownload the current spreadsheet.

This note distributes a spreadsheet in Microsoft Excel that prepares a calendar-table of the total number of dark sky hours on each night for the period of one-year. The spreadsheet also produces a calendar of lunar observing hours. The spreadsheet is configured to prepare calendars based on the observer's latitude and longitude and their preference for a starting 24 hour UTC time.

This spreadsheet can also be used as a deep sky and lunar target planner. Using Microsoft Excel's "Data filter" feature, the next dark sky date that an specific right ascension will be transit your meridian for the next year can be found. Similarly, the next dates and UTC hours that the Moon will be observable when the terminator is at a specific colongitude can be identified over the course of a year.

Observers typically want dark sky calendars that tell them the number of dark sky hours between sunrise and sunset. A civil 24 hour day rarely corresponds to dinural darkness. At northern latitudes there can be about 7 hours of darkness between local sunset and local midnight. The observer can configure the spreadsheet to the best annual local sunset time. For example, at my Salt Lake City, Utah observing site at N41 Lat, W111 Lon, about 5 pm Mountain Standard Time - which is equal to midnight or "zero" hours Universal Time Coordinated - is the best division between hours of light and dark for an entire year. This provides a better division between hours of darkness and daylight for an entire year. The spreadsheet examines the start of each of the 24 hours in a day for one year - or the next 8760 hours - to determine the local horizon altitude of the Sun and the Moon. Based on this, an hour is declared a "dark sky" or "lunar observing" hour, and is summed for the calendars.

The observer can configure the spreadsheet criteria applied to determining whether an hour is a quality dark sky hour and a quality lunar observing period based on:

Sample dark sky and lunar calendars for Salt Lake City, Utah are provided above. This spreadsheet can also be used to prepare an annual observing table of local sidereal time and an annual table of facing lunar colongitudes for a favored observing hour.

The Microsoft Visual Basic for Applications macros that compute the spreadsheet will not run on Excel for Macintosh. A Microsoft Windows Operating System is required.

Algorithms are based on Meeus and Duffet-Smith as documented in the VBA code within the spreadsheet.

Software alternatives include:

Use notes

When opening the spreadsheet on a new system, the spreadsheet may display the "VALUE#" error message in the lunar colongitude field in worksheet "AnnualDarkSkyLunarDetail". Set the UTC starting hour in cell B17 of worksheet "SetCriteria" to 1 and then back to zero. Force recalculation of the spreadsheet. This should clear the error.

It is assumed that the user has a basic working knowledge of Microsoft Excel and Excel's "Data filer" feature.

The spreadsheet uses Visual Basic for Applications (VBA) code to compute lunar terminator position and the Moon's and Sun's sidereal and local horizon positions. You will need to respond "Enable macros" to the standard Excel security prompt to update the spreadsheet.

Only enter data in blue-background cells. Use notes by spreadsheet tab follow:

Revisions and bug fixes


This is amateur astronomy work product.

No copyright is asserted to any original content materials developed and included by this author in this website and the same are released to the public domain. No copyright is asserted as to any scientific fact recited herein. This website was developed for the enjoyment of the amateur astronomical community and as a freeware aid for astronomy clubs, amateurs and secondary school educators. No implied warranty for fitness is provided. Risk of use is solely with the end-user.

Content-Child Safe Ratings

Site tracking

Prepared by K. Fisher fisherka@csolutions.net.