Sunrise Sunset Times

[Pengintai]

I am interested in writing an Excel spreadsheet that computes sunrise and sunset times given latitude and longitude. Preferably, given the basic formulae, I would like to write the spreadsheet myself.

Could anybody assist? Also, I am aware of the numerous web, desktop, and PDA based solutions that are available as a discrete program, but I would prefer to create the spreadsheet myself as an academic exercise.

TIA for any assistance.

[westhawk]

That's a good one! I was just thinking about this yesterday as I watched the sunset. The variable rate of daily change in sunrise/sunset times according to the date with respect to the equinox and solstice is where I run out of math too.

Will wait for Gengis, Mad flt scientist, or J.T. to read this and formulate a response. They can usually be relied on for this kind of thing. Or someone may beat them to the draw!

Best regards,

Westhawk

[Tim Zukas]

You don't need accuracy to better than a minute of time? It's hard to do much better than that anyway, since refraction can't be exactly predicted.

I'm not home now so I can't look it up myself, but Jean Meeus gave formulas in his book Astronomical Algorithms, which should suffice.
Calculating the sun's declination is easy enough as I recall... the equation of time is tougher, but still doable if you don't need it to the second. Then it's just a couple of straightforward spherical-trig formulas.

[Brian Abraham]

Sunrise/Sunset Algorithm
Source:
Almanac for Computers, 1990
published by Nautical Almanac Office
United States Naval Observatory
Washington, DC 20392

Inputs:
day, month, year: date of sunrise/sunset
latitude, longitude: location for sunrise/sunset
zenith: Sun's zenith for sunrise/sunset
offical = 90 degrees 50'
civil = 96 degrees
nautical = 102 degrees
astronomical = 108 degrees

NOTE: longitude is positive for East and negative for West


1. first calculate the day of the year

N1 = floor(275 * month / 9)
N2 = floor((month + 9) / 12)
N3 = (1 + floor((year - 4 * floor(year / 4) + 2) / 3))
N = N1 - (N2 * N3) + day - 30

2. convert the longitude to hour value and calculate an approximate time

lngHour = longitude / 15

if rising time is desired:
t = N + ((6 - lngHour) / 24)
if setting time is desired:
t = N + ((18 - lngHour) / 24)

3. calculate the Sun's mean anomaly

M = (0.9856 * t) - 3.289

4. calculate the Sun's true longitude

L = M + (1.916 * sin(M)) + (0.020 * sin(2 * M)) + 282.634
NOTE: L potentially needs to be adjusted into the range [0,360) by adding/subtracting 360

5a. calculate the Sun's right ascension

RA = atan(0.91764 * tan(L))
NOTE: RA potentially needs to be adjusted into the range [0,360) by adding/subtracting 360

5b. right ascension value needs to be in the same quadrant as L

Lquadrant = (floor( L/90)) * 90
RAquadrant = (floor(RA/90)) * 90
RA = RA + (Lquadrant - RAquadrant)

5c. right ascension value needs to be converted into hours

RA = RA / 15

6. calculate the Sun's declination

sinDec = 0.39782 * sin(L)
cosDec = cos(asin(sinDec))

7a. calculate the Sun's local hour angle

cosH = (cos(zenith) - (sinDec * sin(latitude))) / (cosDec * cos(latitude))

if (cosH > 1)
the sun never rises on this location (on the specified date)
if (cosH < -1)
the sun never sets on this location (on the specified date)

7b. finish calculating H and convert into hours

if if rising time is desired:
H = 360 - acos(cosH)
if setting time is desired:
H = acos(cosH)

H = H / 15

8. calculate local mean time of rising/setting

T = H + RA - (0.06571 * t) - 6.622

9. adjust back to UTC

UT = T - lngHour
NOTE: UT potentially needs to be adjusted into the range [0,24) by adding/subtracting 24

10. convert UT value to local time zone of latitude/longitude

localT = UT + localOffset

++++++++++++++++++++++++++++++++++++++++++++++
Sunrise/Sunset Algorithm Example
Source:
Almanac for Computers, 1990
published by Nautical Almanac Office
United States Naval Observatory
Washington, DC 20392

Inputs:
day, month, year: date of sunrise/sunset
latitude, longitude: location for sunrise/sunset
zenith: Sun's zenith for sunrise/sunset
offical = 90 degrees 50'
civil = 96 degrees
nautical = 102 degrees
astronomical = 108 degrees

NOTE: longitude is positive for East and negative for West

Worked example (from book):
June 25, 1990: 25, 6, 1990
Wayne, NJ: 40.9, -74.3
Office zenith: 90 50' cos(zenith) = -0.01454


1. first calculate the day of the year

N1 = floor(275 * month / 9)
N2 = floor((month + 9) / 12)
N3 = (1 + floor((year - 4 * floor(year / 4) + 2) / 3))
N = N1 - (N2 * N3) + day - 30

Example:
N1 = 183
N2 = 1
N3 = 1 + floor((1990 - 4 * 497 + 2) / 3)
= 1 + floor((1990 - 1988 + 2) / 3)
= 1 + floor((1990 - 1988 + 2) / 3)
= 1 + floor(4 / 3)
= 2
N = 183 - 2 + 25 - 30 = 176

2. convert the longitude to hour value and calculate an approximate time

lngHour = longitude / 15

if rising time is desired:
t = N + ((6 - lngHour) / 24)
if setting time is desired:
t = N + ((18 - lngHour) / 24)

Example:
lngHour = -74.3 / 15 = -4.953
t = 176 + ((6 - -4.953) / 24)
= 176.456

3. calculate the Sun's mean anomaly

M = (0.9856 * t) - 3.289

Example:
M = (0.9856 * 176.456) - 3.289
= 170.626

4. calculate the Sun's true longitude
[Note throughout the arguments of the trig functions
(sin, tan) are in degrees. It will likely be necessary to
convert to radians. eg sin(170.626 deg) =sin(170.626*pi/180
radians)=0.16287]

L = M + (1.916 * sin(M)) + (0.020 * sin(2 * M)) + 282.634
NOTE: L potentially needs to be adjusted into the range [0,360) by adding/subtracting 360

Example:
L = 170.626 + (1.916 * sin(170.626)) + (0.020 * sin(2 * 170.626)) + 282.634
= 170.626 + (1.916 * 0.16287) + (0.020 * -0.32141) + 282.634
= 170.626 + 0.31206 + -0.0064282 + 282.634
= 453.566 - 360
= 93.566

5a. calculate the Sun's right ascension

RA = atan(0.91764 * tan(L))
NOTE: RA potentially needs to be adjusted into the range [0,360) by adding/subtracting 360

Example:
RA = atan(0.91764 * -16.046)
= atan(0.91764 * -16.046)
= atan(-14.722)
= -86.11412

5b. right ascension value needs to be in the same quadrant as L

Lquadrant = (floor( L/90)) * 90
RAquadrant = (floor(RA/90)) * 90
RA = RA + (Lquadrant - RAquadrant)

Example:
Lquadrant = (floor(93.566/90)) * 90
= 90
RAquadrant = (floor(-86.11412/90)) * 90
= -90
RA = -86.11412 + (90 - -90)
= -86.11412 + 180
= 93.886

5c. right ascension value needs to be converted into hours

RA = RA / 15

Example:
RA = 93.886 / 15
= 6.259

6. calculate the Sun's declination

sinDec = 0.39782 * sin(L)
cosDec = cos(asin(sinDec))

Example:
sinDec = 0.39782 * sin(93.566)
= 0.39782 * 0.99806
= 0.39705
cosDec = cos(asin(0.39705))
= cos(asin(0.39705))
= cos(23.394)
= 0.91780

7a. calculate the Sun's local hour angle

cosH = (cos(zenith) - (sinDec * sin(latitude))) / (cosDec * cos(latitude))

if (cosH > 1)
the sun never rises on this location (on the specified date)
if (cosH < -1)
the sun never sets on this location (on the specified date)

Example:
cosH = (-0.01454 - (0.39705 * sin(40.9))) / (0.91780 * cos(40.9))
= (-0.01454 - (0.39705 * 0.65474)) / (0.91780 * 0.75585)
= (-0.01454 - 0.25996) / 0.69372
= -0.2745 / 0.69372
= -0.39570

7b. finish calculating H and convert into hours

if if rising time is desired:
H = 360 - acos(cosH)
if setting time is desired:
H = acos(cosH)

H = H / 15

Example:
H = 360 - acos(-0.39570)
= 360 - 113.310 [ note result of acos converted to degrees]
= 246.690
H = 246.690 / 15
= 16.446

8. calculate local mean time of rising/setting

T = H + RA - (0.06571 * t) - 6.622

Example:
T = 16.446 + 6.259 - (0.06571 * 176.456) - 6.622
= 16.446 + 6.259 - 11.595 - 6.622
= 4.488

9. adjust back to UTC

UT = T - lngHour
NOTE: UT potentially needs to be adjusted into the range [0,24) by adding/subtracting 24

Example:
UT = 4.488 - -4.953
= 9.441
= 9h 26m

10. convert UT value to local time zone of latitude/longitude

localT = UT + localOffset

Example:
localT = 9h 26m + -4
= 5h 26m
= 5:26 am EDT
++++++++++++++++++++++++++++++++++++++++++++++
Courtesy of Ed Williams at http://williams.best.vwh.net

[Old Smokey]

Brian Abraham's reply is absolutely correct, but he forgot to allow for Nutation. That should make the formulae valid for only the next 200 years or so, you'd better re-write the programme after that ;)

In the programmes that I've written I've always used 'Half Sun' to 'Half Sun' as a slightly conservative approach for aviation, but if you're writing a programme for Ramadan, you'd better apply 'Upper limb' to 'Upper limb'.

Happy Ramadan and Regards,

Old Smokey

[john_tullamarine]

.. those of us who are a bit lazy would just run some regressions on published graphs and do it the easy way ...

[Old Smokey]

Yeah John_T, I never could see things the simple way, obstacle polygons etc.......:ok:

Regards,

Old Smokey

[Brian Abraham]

Old Smokey and john_tullamarine,
Is there anything you two gents don't know? Always find your encyclopedic knowledge educational and entertaining.

Regards,
Brian

[popay]

Dear Sirs,
is it possible just to get that spreadsheet for the passengers asking for prayer time and so on. I am quite interested to know it my self but obviously too lazy to follow suggested steps.
Thanks in advance.
:O

[Brian Abraham]

Reading posts on how some airlines operate prayer time would be all the time.

[john_tullamarine]

"Old Smokey and john_tullamarine,
Is there anything you two gents don't know?"

Not quite that black and white ... but we do try not to shoot our mouths off too much on subjects for which we are lacking "knowledge, for the demonstration of"

[SuperRanger]

pengintai,

if you own a palm pilot, things will be a lot simpler ;)

http://homepage.sunrise.ch/mysunrise/rasch/

if you are looking for islamic prayer times, may i recommend
http://www.freewarepalm.com/religion/salatschedule.shtml

[Old Smokey]

"Old Smokey and john_tullamarine,
Is there anything you two gents don't know?"

j_t is eminently much more practical, with a fountain of knowledge that would stagger NASA. For me, I just wait until j_t answers and then pick up the crumbs (that way he doesn't get to jump on my hastily concocted, un-thought out responses). Mind you, mutt does a pretty good job of keeping me in line.

I've noticed that we both tend to end sentences with................, which, I think implies - make up the rest for yourself. It's called a hanging something or other in grammar, English was my weakest subject, for which, my cop-out is blaming a certain amount of Irish ancestry............:O

Regards,

Old Smokey

[john_tullamarine]

.. good heavens .. that makes three of us with the luck o' the Irish ... I really think we should have a threesome of Guinness sometime along the way ..

Can still recall that wide runway and the smoking tyres ..

[LGB]

Pengintai,

I am working on a similar project (to be implemented into a logbook), to calculate how much time is day and night time for a given flight (in case you are sleeping when the sun rises/sets!)

Some of the obstacles are your altitude, giving more day time, as well as the problem of position and time change at the same time. I have thought of a more simple solution where sunset/rise is calculated for departure and destination aerodrome, and then the average is taken. This will be highly inaccurate though, for certain flights.

[Milt]

Most GPS receivers have the facility built in and can come up with an answer in a flash.