Sunrise / Sunset

Does anybody know of a function / add-in / piece of programming that will
generate the sunrise and sunset times for a location based on it's latitude
and longitude and the date.

I know there are tables on the intenet - but I would like to generate the
data within my worksheet - rather than copy data from the internet and spend
ages formatting it.

Worth a try!

All the best.

On Wed, 7 Sep 2005 10:10:07 -0700, "Peter Barrett"
<PeterBarrett@discussions.microsoft.com> wrote:

>Does anybody know of a function / add-in / piece of programming that will
>generate the sunrise and sunset times for a location based on it's latitude
>and longitude and the date.
>
>I know there are tables on the intenet - but I would like to generate the
>data within my worksheet - rather than copy data from the internet and spend
>ages formatting it.
>
>Worth a try!
>
>All the best.


Try

http://www.ecy.wa.gov/programs/eap/models/

The 6th item down is an Excel Sunrise/Sunset calculator.

HTH
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Richard

That is indeed very helpful.

I am now trying to figure out if (and how) it is possible to use the
functions created in the spreadsheet on the website in my own spreadsheet so
that I can generate sunrise and sunset times for specific locations on
specific days. Any advice?

PS - if you happen to be interested in these things, I also came across a
useful program at http://www.fv01.dial.pipex.com/world.htm?suntimes.shtml

All the best.

Peter

"Richard Buttrey" wrote:

> On Wed, 7 Sep 2005 10:10:07 -0700, "Peter Barrett"
> <PeterBarrett@discussions.microsoft.com> wrote:
>
> >Does anybody know of a function / add-in / piece of programming that will
> >generate the sunrise and sunset times for a location based on it's latitude
> >and longitude and the date.
> >
> >I know there are tables on the intenet - but I would like to generate the
> >data within my worksheet - rather than copy data from the internet and spend
> >ages formatting it.
> >
> >Worth a try!
> >
> >All the best.
>
>
> Try
>
> http://www.ecy.wa.gov/programs/eap/models/
>
> The 6th item down is an Excel Sunrise/Sunset calculator.
>
> HTH
> __
> Richard Buttrey
> Grappenhall, Cheshire, UK
> __________________________
>

On Fri, 9 Sep 2005 01:25:28 -0700, "Peter Barrett"
<PeterBarrett@discussions.microsoft.com> wrote:

>Richard
>
>That is indeed very helpful.
>
>I am now trying to figure out if (and how) it is possible to use the
>functions created in the spreadsheet on the website in my own spreadsheet so
>that I can generate sunrise and sunset times for specific locations on
>specific days. Any advice?


I suppose it depends on how detailed your spreadsheet is. If it was
reasonably straightforward I'd be inclined to take the entire
workbook from the site, (subject to any copyright notices and
accreditation requirements), and re-build your particular application
on top of it as a sort of front end.

If you've got specific locations that you use all the time, then you
could have a table of those locations, and link them to a drop down
ListBox in a new menu. When a choice is made from the menu, you could
get the macro to update the pre-existing cells of the sunrise/sunset
workbook. Ditto with selecting particular days or any other
parameters.


If you don't want to do that, then you could study the functions in
the workbook, pick out all the ones that are applicable and re-create
them in yiour current workbook.

I suspect on balance that you might find the first approach easier.

>PS - if you happen to be interested in these things, I also came across a
>useful program at http://www.fv01.dial.pipex.com/world.htm?suntimes.shtml

Thanks, I'll have a look shortly,

Regards


>All the best.
>
>Peter
>
>"Richard Buttrey" wrote:
>
>> On Wed, 7 Sep 2005 10:10:07 -0700, "Peter Barrett"
>> <PeterBarrett@discussions.microsoft.com> wrote:
>>
>> >Does anybody know of a function / add-in / piece of programming that will
>> >generate the sunrise and sunset times for a location based on it's latitude
>> >and longitude and the date.
>> >
>> >I know there are tables on the intenet - but I would like to generate the
>> >data within my worksheet - rather than copy data from the internet and spend
>> >ages formatting it.
>> >
>> >Worth a try!
>> >
>> >All the best.
>>
>>
>> Try
>>
>> http://www.ecy.wa.gov/programs/eap/models/
>>
>> The 6th item down is an Excel Sunrise/Sunset calculator.
>>
>> HTH
>> __
>> Richard Buttrey
>> Grappenhall, Cheshire, UK
>> __________________________
>>

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

"Peter Barrett" <PeterBarrett@discussions.microsoft.com> wrote in message
news:357FB60B-CBA0-4415-BD82-29C5575C52E9@microsoft.com...
> Does anybody know of a function / add-in / piece of programming that will
> generate the sunrise and sunset times for a location based on it's
latitude
> and longitude and the date.
>
> I know there are tables on the intenet - but I would like to generate the
> data within my worksheet - rather than copy data from the internet and
spend
> ages formatting it.

Take a look at
http://personal.inet.fi/cool/jjlammi/stuff.html

Markus

Richard

Came to a similar conclusion about using the sunrise spreadsheet as a front
end to my spreadsheet.

Thanks for your ideas.

Peter

"Richard Buttrey" wrote:

> On Fri, 9 Sep 2005 01:25:28 -0700, "Peter Barrett"
> <PeterBarrett@discussions.microsoft.com> wrote:
>
> >Richard
> >
> >That is indeed very helpful.
> >
> >I am now trying to figure out if (and how) it is possible to use the
> >functions created in the spreadsheet on the website in my own spreadsheet so
> >that I can generate sunrise and sunset times for specific locations on
> >specific days. Any advice?
>
>
> I suppose it depends on how detailed your spreadsheet is. If it was
> reasonably straightforward I'd be inclined to take the entire
> workbook from the site, (subject to any copyright notices and
> accreditation requirements), and re-build your particular application
> on top of it as a sort of front end.
>
> If you've got specific locations that you use all the time, then you
> could have a table of those locations, and link them to a drop down
> ListBox in a new menu. When a choice is made from the menu, you could
> get the macro to update the pre-existing cells of the sunrise/sunset
> workbook. Ditto with selecting particular days or any other
> parameters.
>
>
> If you don't want to do that, then you could study the functions in
> the workbook, pick out all the ones that are applicable and re-create
> them in yiour current workbook.
>
> I suspect on balance that you might find the first approach easier.
>
> >PS - if you happen to be interested in these things, I also came across a
> >useful program at http://www.fv01.dial.pipex.com/world.htm?suntimes.shtml
>
> Thanks, I'll have a look shortly,
>
> Regards
>
>
> >All the best.
> >
> >Peter
> >
> >"Richard Buttrey" wrote:
> >
> >> On Wed, 7 Sep 2005 10:10:07 -0700, "Peter Barrett"
> >> <PeterBarrett@discussions.microsoft.com> wrote:
> >>
> >> >Does anybody know of a function / add-in / piece of programming that will
> >> >generate the sunrise and sunset times for a location based on it's latitude
> >> >and longitude and the date.
> >> >
> >> >I know there are tables on the intenet - but I would like to generate the
> >> >data within my worksheet - rather than copy data from the internet and spend
> >> >ages formatting it.
> >> >
> >> >Worth a try!
> >> >
> >> >All the best.
> >>
> >>
> >> Try
> >>
> >> http://www.ecy.wa.gov/programs/eap/models/
> >>
> >> The 6th item down is an Excel Sunrise/Sunset calculator.
> >>
> >> HTH
> >> __
> >> Richard Buttrey
> >> Grappenhall, Cheshire, UK
> >> __________________________
> >>
>
> __
> Richard Buttrey
> Grappenhall, Cheshire, UK
> __________________________
>

Markus

Thanks for responding.

I'll have a look at this site when I return home in a few days time.

All the best.

Peter

"Markus L" wrote:

> "Peter Barrett" <PeterBarrett@discussions.microsoft.com> wrote in message
> news:357FB60B-CBA0-4415-BD82-29C5575C52E9@microsoft.com...
> > Does anybody know of a function / add-in / piece of programming that will
> > generate the sunrise and sunset times for a location based on it's
> latitude
> > and longitude and the date.
> >
> > I know there are tables on the intenet - but I would like to generate the
> > data within my worksheet - rather than copy data from the internet and
> spend
> > ages formatting it.
>
> Take a look at
> http://personal.inet.fi/cool/jjlammi/stuff.html
>
> Markus
>
>
>

Here's my suggestion:

1. Download Greg Pelletier's excel file (filename "twilight.xls")
Greg's page, get the 6th link down:
http://www.ecy.wa.gov/programs/eap/models.html

2. Open the file, and Save-As an Excel Add-In file (new filename "twilight.xla")
Basic add-in info I referenced to get it working:
http://www.fontstuff.com/vba/vbatut03.htm#addin

3. Install in add-in.
For me / Excel 2013:
File > Options > Add-Ins > Manage: Excel Add-ins (dropdown) > Go... > Check the "Twilight" box*

4. You're done. The sunrise, sunset, and the rest of greg's functions should now work just like any other function, in all of your workbooks as long as the add-in is installed. Refer to the twilight.xls file for instructions on using the functions, but basically, just type "=sunrise(lat, lon, year, month, day, timezone, dlstime)" to return the time of sunrise.

*if the new add-in doesn't populate automatically: I think you need to save the .xla to your app data folder (User/AppData/Roaming/Microsoft/AddIns). Google will have more on this. It wasn't an issue for me...

Thoughts: Greg has done some really great work! No changes are needed whatsoever to get this working; just save as an add-in, install and you're done. Now "sunrise" and "sunset" are functions available in all your workbooks. No need to add VBA code or new functions to each work book. I always forget the order of the inputs, but this is in the twilight.xls and is very clear.

I use an If/And structure with greg's VBA to check if time is during the day or night. Works like a charm!
If(And(Sunrise<CurrentTime, CurrentTime>Sunset),Day,Night)

Hope it helps!

YOu Helped me and Im very interested in this thanks for the info.