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:
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:
This worksheet stores the observing point location.
In the blue highlighted cells, enter the terresterial longitude and latitude for your observing point in integer degrees, minutes and seconds. The remaining sheets use this information to compute the Moon's and Sun's local horizon altitude and azimuth.
Enter your UTC to local time offset in the blue highlighted cell B6.
The remainder of the spreadsheet is driven by the starting date year, Month, Day and integer UTC hour in blue highlighted cells B14 throug B17 and the day "break" hour in cell B23. In the hours field only enter an integer bewteen 0 and 23. Note that the time entered in the hours field is UTC, not local time.
Cells B14 throug B17 set the starting date and time of calendar computations.
The key entry in cell B23 sets the break hour between a calendar day. The observer uses this cell to 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.
Using this observing time data, the current position of the lunar terminator and the Moon's and Sun's altitude and azimuth in the observing point's local horizon system are computed.
Criteria for a good "dark sky" and a "lunar observing" hour are entered in cells B23-B24 and B27-B28.
Cells B14 through B17 is the primary means by which recalculation of the spreadsheet is forced. To force recalculation, simply change the hours value in cell B17.
Recalculation takes about 90 seconds on a 1.7 Ghz IBM personal computer.
The worksheet is the primary detail display of computed results from the settings entered in worksheet "SetCriteria". The worksheet displays results for 8760 (24 x 365) consecutive hours based on the starting time entered in worksheet "SetCriteria".
This worksheet can also be used as a lunar or deep sky object planner. For deep sky planning, column "M" can be filtered based on the right ascension transiting your meridian - that is local sidereal time. (Column "L" is LST in right ascension, column "M" is LST in decimal degrees.) Also filtering on column "N" - IsDarkSkyHour - will display a list of dark sky hours for the next year when an object will transit. For lunar planning, column "H" - Lunar Facing Colongitude (LunarFacingClong) - can be filtered for a range of terminator positions on the face of the Moon. Also filtering on column "P" - IsLunarObsHour - gives a list of the hours over the next year when the lunar feature will be on the terminator and above the horizon.
The facing lunar terminator colongitude is expressed as a value bewteen positive 0 and 90 degrees for east selenographic longitude and between negative 0 and 90 degrees for west selenographic longitude.
This worksheet is a pivot-table summary of worksheet AnnualDarkSkyLunarDetail. It summarizes the total hours of dark sky hours for each day over the next year. Columns are months 1 through 12. Rows are days 1 through 31.
This pivot table needs to be refreshed after each change in the calendar start date-time or day "break" UTC hour in worksheet SetCriteria. To refresh the pivot table, right-click on the upper right hand corner of the table and select "Refresh" from the menu options.
This worksheet is a pivot-table summary of worksheet AnnualDarkSkyLunarDetail. It summarizes the total hours of lunar observing hours for each day over the next year. Columns are months 1 through 12. Rows are days 1 through 31.
This pivot table needs to be refreshed after each change in the calendar start date-time or day "break" UTC hour in worksheet SetCriteria. To refresh the pivot table, right-click on the upper right hand corner of the table and select "Refresh" from the menu options.
This worksheet is a pivot-table summary of worksheet AnnualDarkSkyLunarDetail. It summarizes the facing colongitude of the lunar terminator on each day for one year based on a prefered UTC observing hour. Typically, this author observers around 4 UTC. It supplements the information on when the Moon is visible in worksheet AnnualLunarObsSummary.
The facing lunar terminator colongitude is expressed as a value bewteen positive 0 and 90 degrees for east selenographic longitude and between negative 0 and 90 degrees for west selenographic longitude.
This pivot table needs to be refreshed after each change in the calendar start date-time or day "break" UTC hour in worksheet SetCriteria. To refresh the pivot table, right-click on the upper right hand corner of the table and select "Refresh" from the menu options.
This worksheet is a pivot-table summary of worksheet AnnualDarkSkyLunarDetail. It summarizes transiting meridian right ascension for an observing point for one year - that is the observer's local sidereal time - based on a selected UTC observing hour. Typically, this author observers around 4 UTC. It supplements the information on when dark skies are available in worksheet AnnualDarkSkySummary.
This pivot table needs to be refreshed after each change in the calendar start date-time or day "break" UTC hour in worksheet SetCriteria. To refresh the pivot table, right-click on the upper right hand corner of the table and select "Refresh" from the menu options.
This worksheet displays two charts of right ascension values. The first chart visible on the screen is in decimal degrees. The second chart, off-screen to the right of the first table, displays local sidereal time in a "HHMMSS" format.Worksheets ExportHTMLDarkSky, ExportHTMLLunarObs, ExportHTMLCoLongSum and ExportHTMLAnnualRASum can used to make generic HTML tables. Worksheet ExportHTMLTemplate contains an html document template into which an html calendar table can be pasted.
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.
Prepared by K. Fisher fisherka@csolutions.net.