#Excel: Getting the Sunrise and Sunset Times From an Address by David Hager

 

A while back I worked for quite some time on returning the sunrise and sunset from an address using VBA. I was not successful, so I shelved that project. But, after publishing several popular articles using Excel’s web functions, I decided to revisit this project using those functions. As always, implementation of this technique requires the appropriate xml data sources and the corresponding return values. I could not locate a single xml source to do this, so I finally found 3 xml sources that, if combined, would theoretically provide me with the desired result. Here are the 3 API sources.

http://maps.googleapis.com/maps/api

http://api.timezonedb.com/ ‘Requires a free API key

http://new.earthtools.org/

The 1^st API takes a detailed address and returns latitude and longitude for that location. The 2^nd API takes latitude and longitude as input and returns a daylight savings time (dst) value and a GMT offset (gmtOffset) value. The 3^rd API takes latitude, longitude, dst, gmtOffset and the current day and returns the sunrise and sunset times. For the demonstration, address is a defined name formula.

address =”4163 W. Sparrow St., Orange, Tx.”

However, a named cell could also be used to contain the address.

The two formulas needed to return latitude and longitude from googleapis.com are

latitude= FILTERXML(WEBSERVICE(“http://maps.googleapis.com/maps/api/geocode/xml?address=”&ENCODEURL(address)&”&sensor=false”),”//lat”)

longitude= FILTERXML(WEBSERVICE(“http://maps.googleapis.com/maps/api/geocode/xml?address=”&ENCODEURL(address)&”&sensor=false”),”//lng”)

Note that the Excel ENCODEURL function is needed to convert the address to a form compatible with the API.

The latitude and longitude coordinates are then used as parameters in the timezonedb.com API to return values for daylight savings time and GMT offset. An API key is required by the web site owner, but you can register and get a free one at https://timezonedb.com/register. The API key is stored in the defined name API_Key.

dst = FILTERXML(WEBSERVICE(“http://api.timezonedb.com/v2/get-time-zone?key=”&API_Key&”&format=xml&by=position&lat=”&latitude&”&lng=”&longitude&””),”//dst”)

gmtOffset = (FILTERXML(WEBSERVICE(“http://api.timezonedb.com/v2/get-time-zone?key=”&API_Key&”&format=xml&by=position&lat=”&latitude&”&lng=”&longitude&””),”//gmtOffset”)/3600)-1

Finally, all of the parameters from the previous 2 APIs plus the TODAY function are used in the earthtools.org API to reurn sunrise and sunset times for the specified location.

sunrise= TEXT(FILTERXML(WEBSERVICE(“http://new.earthtools.org/sun/”&latitude&”/”&longitude&”/”&DAY(TODAY())&”/”&MONTH(TODAY())&”/”&gmtOffset&”/”&dst&””),”//sunrise”),”hh:mm:ss”)

sunset= TEXT(FILTERXML(WEBSERVICE(“http://new.earthtools.org/sun/”&latitude&”/”&longitude&”/”&DAY(TODAY())&”/”&MONTH(TODAY())&”/”&gmtOffset&”/”&dst&””),”//sunset”),”hh:mm:ss”)

Note that the date parameters can be changed if the day to get results for is not today.

The results of the formulas used in this technique can be seen in this figure.

The formulas have been converted to values in the model due to my inability to share my API key, but you add back in what you want to see with the formulas described in this article (and in defined names).

You can download the file here.

SunriseSunsetInfo