3
$\begingroup$

I am looking for a way to roughly calculate sunrise and sunset for a given lon/lat in mid Europe for each day of a year using a spreadsheet application. The thing doesn’t need to be very precise, but it should give a rough idea like when to switch on street lights—earlier in winter than in summer.

I have read various pages on that topic now, but they deal with the question in a more complicated (and surely, more precise) way than what I need. I have had such a spreadsheet in the past (which I did build myself), but I don’t have it anymore and the sources I used seem to have vanished from the internet.

What I want is to make a spread sheet with one row per day, where column

A𝑛 = the date (here, input as spreadsheet date)
B𝑛 = lon (W is negative, E is positive)
C𝑛 = lat (S is negative, N is positive)
D𝑛 = time zone (here, input as spreadsheet time)

What I needed is two formulas,

E𝑛 = true noon
F𝑛 = day length

so that I can get sunrise and sunset as

G𝑛 = E𝑛 - (F𝑛/2)
H𝑛 = E𝑛 + (F𝑛/2)

I think in the spreadsheet I once had, it took two sine calculations and some constants to get this done.

Example spreadsheet

Please help me get this working again or to find the sources where it is explained.

$\endgroup$
3
  • $\begingroup$ Related: astronomy.stackexchange.com/a/39685/16685 $\endgroup$
    – PM 2Ring
    Dec 12, 2022 at 10:15
  • $\begingroup$ Why do you want to know sunrise / sunset for a point in the Indian Ocean (near Somalia), and why are you using the +1:00 timezone for it? Have you mixed up latitude & longitude? $\endgroup$
    – PM 2Ring
    Dec 13, 2022 at 12:57
  • $\begingroup$ True, column labels are reversed here. The order is as from GPS, first value is N > 0, S < 0, second value is E > 0, W < 0. $\endgroup$
    – Maron
    Aug 23, 2023 at 6:41

2 Answers 2

1
$\begingroup$

Another way to do it is to compute the RA/Dec of the Sun, then use the algorithm from Meeeus' Astronomical Algorithms1,2 to compute the rise and set times for a given RA/Dec.

Below is a the algorithm for the low precision Sun position algorithm provided in the Astronomical Almanac, it is accurate to 1 degree between 1950 and 2050. And an implementation in JavaScript follows.

\begin{align*} n&=jd-2451545.0 \\ L&=280.460+0.9856474*n \\ g&=375.528+.9856003*n \\ \lambda &=L+1.915*\sin g+0.020*\sin(2*g) \\ \beta&=0.0 \\ \epsilon &=23.439-0.0000004*n \\ \tan \alpha &=\frac{\cos(\epsilon)*\sin(\lambda)}{\cos(\lambda)} \\ \sin \delta &=\sin(\epsilon)*\sin(\lambda) \end{align*}

Where $\lambda$ is the ecliptic longitude, $\beta$ is the ecliptic latitude (always 0), $\alpha$ is the Right Ascension, and $\delta$ is the Declination. Implementation in Javascript:

function sunPosition(jd)    {
    const torad=Math.PI/180.0;
    const n=jd-2451545.0;
    let L=(280.460+0.9856474*n)%360;
    let g=((375.528+.9856003*n)%360)*torad;
    if(L<0){L+=360;}
    if(g<0){g+=Math.PI*2.0;}

    const lamba=(L+1.915*Math.sin(g)+0.020*Math.sin(2*g))*torad;
    const beta=0.0;
    const eps=(23.439-0.0000004*n)*torad;
    let ra=Math.atan2(Math.cos(eps)*Math.sin(lamba),Math.cos(lamba));
    const dec=Math.asin(Math.sin(eps)*Math.sin(lamba));
    if(ra<0){ra+=Math.PI*2;}
    return [ra/torad/15.0,dec/torad];
}

To compute the rise and set times:

$$ \cos H_0 = \dfrac{\sin h_0 - \sin \varphi \sin \delta }{\cos \varphi \cos \delta} $$ If $\cos H_0$ < -1 or > 1, the point is either always above or below the horizon. $$ \begin{align} T &= (jd-2451545.0)/36525.0 \\ \Theta_0 &= 280.46061837+360.98564736629*(jd-2451545.0)+0.000387933T^2 - T^3/38710000.0 \end{align} $$

\begin{cases} transit & \dfrac{\delta + L - \Theta_0 }{360^{\circ}} \\ \\ rise & transit - \dfrac{H_0}{360^{\circ}} \\ \\ set & transit + \dfrac{H_0}{360^{\circ}} \end{cases}

$ jd $ is the Julian Date for the date in question.
$\delta$ Declination
$L$ Longitude
$\varphi$ Latitude
$h_0$ Apparent rise or set angle, -0.8333 for the Sun, +0.125 for the Moon, and -0.5667 for most other objects.
$\Theta_0$ Greenwich sidereal time at 0h for the day in question.

And this page has a JavaScript implementation to compute the Sun rise, set, and transit using the algorithms above.

You might also be interested in the book "Practical Astronomy with your Calculator or Spreadsheet" by Peter Duffett-Smith and Jonathan Zwart. It has similar algorithms, but is more specific to doing it with a spreadsheet.


$\endgroup$
0
$\begingroup$

I finally found the source, or at least something like the source I had once back then. So I got this to work again:

Sunrise—sunset table

These are the formulas needed:

E2 =DAYS(A2;DATE(YEAR(A2);1;0))
F2 =(12+0.1752*SIN(0.03343*E2+0.5474)+0.134*SIN(0.0182334*E2-0.1939)-C2/15+24*D2)/24
G2 =0.40954*SIN(0.0172*(E2-79.35))
H2 =ACOS((-0.0145-SIN(B2*PI()/180)*SIN(G2))/(COS(B2*PI()/180)*COS(G2)))/PI()
I2 =F2-H2/2
J2 =F2+H2/2

As per the source I took it from, this is imprecise ±5 minutes.

$\endgroup$
1
  • $\begingroup$ Your location in the spreadsheet is in the Indian Ocean, not mid Europe. It's close enough to the equator that days would be close to twelve hours year round. If your latitude is expressed in radians, it's an impossible latitude: can't be greater pi/2. $\endgroup$
    – stretch
    Dec 13, 2022 at 15:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .