# Sunrise Sunset Times

Thread Starter

Join Date: Nov 1999

Posts: 24

Likes: 0

Received 0 Likes
on
0 Posts

**Sunrise Sunset Times**

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.

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.

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

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.

Join Date: Aug 2003

Location: Sale, Australia

Age: 80

Posts: 3,832

Likes: 0

Received 0 Likes
on
0 Posts

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

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

Join Date: Jun 2004

Location: Australia

Posts: 1,843

Likes: 0

Received 0 Likes
on
0 Posts

**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

Join Date: Aug 2003

Location: Sale, Australia

Age: 80

Posts: 3,832

Likes: 0

Received 0 Likes
on
0 Posts

Old Smokey and john_tullamarine,

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

Regards,

Brian

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

Regards,

Brian

Join Date: Apr 2004

Location: Europe

Posts: 467

Likes: 0

Received 0 Likes
on
0 Posts

**Spreadsheet**

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.

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.

Moderator

"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"

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"

Join Date: Nov 2001

Location: KUL

Posts: 214

Likes: 0

Received 0 Likes
on
0 Posts

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...schedule.shtml

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...schedule.shtml

Join Date: Jun 2004

Location: Australia

Posts: 1,843

Likes: 0

Received 0 Likes
on
0 Posts

"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............

Regards,

Old Smokey

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............

Regards,

Old Smokey

Moderator

.. 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 ..

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

Join Date: Feb 2002

Location: -

Posts: 174

Likes: 0

Received 0 Likes
on
0 Posts

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.

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.