C
Chuck
Is there an equation that will calculate the phases of the moon?
Chuck
Chuck
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
B
Bernie Deitrick
Chuck,
The code below doesn't take into account the variability of the lunar orbit,
but it is close. It also doesn't take into account DST, which might flip a
day here or there when the actual times are near midnight.
HTH,
Bernie
MS Excel MVP
Dim D1 As Date
Dim D2 As Date
Dim strMsg As String
Dim CurDate As Date
'Average Revolution = 29.5305556 days = 29 days & 12 hrs & 44 minutes
Const RL = 29.5305556
Sub ShowMoonDates()
D1 = DateValue("Jan 26, 2009") + TimeValue(" 2:55:00") ' Reference Date
for New MOON, EST
D2 = DateValue("Feb 9, 2009") + TimeValue("9:49:00") ' Reference Date
for Full MOON, EST
CurDate = Date
On Error GoTo ErrHandler
strMsg = "Dates not corrected for Daylight savings time." & vbCrLf
While D1 <= CurDate
D1 = D1 + RL
Wend 'Next New moon
While D2 <= CurDate
D2 = D2 + RL
Wend 'next Full Moon
strMsg = strMsg + "Next new moon will be on " & Format(D1, "mmm dd,
yyyy") & vbCrLf
strMsg = strMsg + "Next full moon will be on " & Format(D2, "mmm dd,
yyyy")
ErrHandler:
MsgBox strMsg, vbExclamation, "Moon Dates"
End Sub
The code below doesn't take into account the variability of the lunar orbit,
but it is close. It also doesn't take into account DST, which might flip a
day here or there when the actual times are near midnight.
HTH,
Bernie
MS Excel MVP
Dim D1 As Date
Dim D2 As Date
Dim strMsg As String
Dim CurDate As Date
'Average Revolution = 29.5305556 days = 29 days & 12 hrs & 44 minutes
Const RL = 29.5305556
Sub ShowMoonDates()
D1 = DateValue("Jan 26, 2009") + TimeValue(" 2:55:00") ' Reference Date
for New MOON, EST
D2 = DateValue("Feb 9, 2009") + TimeValue("9:49:00") ' Reference Date
for Full MOON, EST
CurDate = Date
On Error GoTo ErrHandler
strMsg = "Dates not corrected for Daylight savings time." & vbCrLf
While D1 <= CurDate
D1 = D1 + RL
Wend 'Next New moon
While D2 <= CurDate
D2 = D2 + RL
Wend 'next Full Moon
strMsg = strMsg + "Next new moon will be on " & Format(D1, "mmm dd,
yyyy") & vbCrLf
strMsg = strMsg + "Next full moon will be on " & Format(D2, "mmm dd,
yyyy")
ErrHandler:
MsgBox strMsg, vbExclamation, "Moon Dates"
End Sub
B
Bernie Deitrick
Chuck,
There is a page at the Naval Observatory:
http://aa.usno.navy.mil/data/docs/MoonPhase.php#y2009
I just picked two from early this year, and adjusted UTC to EST.
HTH,
Bernie
MS Excel MVP
There is a page at the Naval Observatory:
http://aa.usno.navy.mil/data/docs/MoonPhase.php#y2009
I just picked two from early this year, and adjusted UTC to EST.
HTH,
Bernie
MS Excel MVP
C
Chuck
Bernie,
Thank you. Exactly what I was looking for. Out of curiosity, how did you
arrive at the two reference days? However, now that I have the reference days,
I should be able to calculate the new moons indefinitely on into the future.
Chuck
Thank you. Exactly what I was looking for. Out of curiosity, how did you
arrive at the two reference days? However, now that I have the reference days,
I should be able to calculate the new moons indefinitely on into the future.
Chuck
C
Chuck
On Thu, 26 Mar 2009 08:50:59 -0400, "Bernie Deitrick" <deitbe @ consumer dot
org> wrote:
Burnie,
I have an equation that accepts an input of a year (2009) and from this single
input it calculates the Month and Day for Easter of that year.
=FLOOR("5/"&DAY(MINUTE(YEAR({year})/38)/2+56)&"/"&YEAR({year}),7)-34
It is not my equation. It is something I found on the internet a fair number
of years ago. Since Easter is the first Sunday after the first full moon on or
after the Vernal Equinox (March 21), the equation some how calculates the date
for at least one full moon. I've tried to 'pull out' that full moon date. But
I'm just not smart enough.. If the date of that full moon can be had from a
single equation, that date can be the reference date to calculate full moons
for the rest of the year using you subroutine.
org> wrote:
Burnie,
I have an equation that accepts an input of a year (2009) and from this single
input it calculates the Month and Day for Easter of that year.
=FLOOR("5/"&DAY(MINUTE(YEAR({year})/38)/2+56)&"/"&YEAR({year}),7)-34
It is not my equation. It is something I found on the internet a fair number
of years ago. Since Easter is the first Sunday after the first full moon on or
after the Vernal Equinox (March 21), the equation some how calculates the date
for at least one full moon. I've tried to 'pull out' that full moon date. But
I'm just not smart enough.. If the date of that full moon can be had from a
single equation, that date can be the reference date to calculate full moons
for the rest of the year using you subroutine.
B
Bernie Deitrick
I don't think that the formula calculates anything about the full moon - it is most likely that the
pattern of Easter Sundays fit some other discernable pattern (which I certainly cannot figure out).
But setting one full moon should be able to predict full moons many years into the future or past -
with a slight modification, the code correctly return the moon dates in 1901.
From the same contest that gave that function:
=TRUNC(DATE(J,7,-CODE(MID("NYdQ\JT_LWbOZeR]KU`",MOD(J,19)+1,1)))/7)*7+8
where J is the year...
But this VBA Function may give you a better idea of how the math is done:
Function EASTER(Yr As Integer) As Long
Dim Century As Integer
Dim Sunday As Integer
Dim Epact As Integer
Dim Golden As Integer
Dim LeapDayCorrection As Integer
Dim SynchWithMoon As Integer
Dim N As Integer
Golden = (Yr Mod 19) + 1
Century = Yr \ 100 + 1
LeapDayCorrection = 3 * Century \ 4 - 12
SynchWithMoon = (8 * Century + 5) \ 25 - 5
Sunday = 5 * Yr \ 4 - LeapDayCorrection - 10
Epact = (11 * Golden + 20 + SynchWithMoon - LeapDayCorrection) Mod 30
If Epact < 0 Then Epact = Epact + 30
If (Epact = 25 And Golden > 11) Or Epact = 24 Then Epact = Epact + 1
N = 44 - Epact
If N < 21 Then N = N + 30
N = N + 7 - ((Sunday + N) Mod 7)
EASTER = DateSerial(Yr, 3, N)
End Function
HTH,
Bernie
MS Excel MVP
pattern of Easter Sundays fit some other discernable pattern (which I certainly cannot figure out).
But setting one full moon should be able to predict full moons many years into the future or past -
with a slight modification, the code correctly return the moon dates in 1901.
From the same contest that gave that function:
=TRUNC(DATE(J,7,-CODE(MID("NYdQ\JT_LWbOZeR]KU`",MOD(J,19)+1,1)))/7)*7+8
where J is the year...
But this VBA Function may give you a better idea of how the math is done:
Function EASTER(Yr As Integer) As Long
Dim Century As Integer
Dim Sunday As Integer
Dim Epact As Integer
Dim Golden As Integer
Dim LeapDayCorrection As Integer
Dim SynchWithMoon As Integer
Dim N As Integer
Golden = (Yr Mod 19) + 1
Century = Yr \ 100 + 1
LeapDayCorrection = 3 * Century \ 4 - 12
SynchWithMoon = (8 * Century + 5) \ 25 - 5
Sunday = 5 * Yr \ 4 - LeapDayCorrection - 10
Epact = (11 * Golden + 20 + SynchWithMoon - LeapDayCorrection) Mod 30
If Epact < 0 Then Epact = Epact + 30
If (Epact = 25 And Golden > 11) Or Epact = 24 Then Epact = Epact + 1
N = 44 - Epact
If N < 21 Then N = N + 30
N = N + 7 - ((Sunday + N) Mod 7)
EASTER = DateSerial(Yr, 3, N)
End Function
HTH,
Bernie
MS Excel MVP
C
Chuck
Thank you.
I'll spend some time on it later today.
Chuck
I'll spend some time on it later today.
Chuck
I don't think that the formula calculates anything about the full moon - it is most likely that the
pattern of Easter Sundays fit some other discernable pattern (which I certainly cannot figure out).
But setting one full moon should be able to predict full moons many years into the future or past -
with a slight modification, the code correctly return the moon dates in 1901.
From the same contest that gave that function:
=TRUNC(DATE(J,7,-CODE(MID("NYdQ\JT_LWbOZeR]KU`",MOD(J,19)+1,1)))/7)*7+8
where J is the year...
But this VBA Function may give you a better idea of how the math is done:
Function EASTER(Yr As Integer) As Long
Dim Century As Integer
Dim Sunday As Integer
Dim Epact As Integer
Dim Golden As Integer
Dim LeapDayCorrection As Integer
Dim SynchWithMoon As Integer
Dim N As Integer
Golden = (Yr Mod 19) + 1
Century = Yr \ 100 + 1
LeapDayCorrection = 3 * Century \ 4 - 12
SynchWithMoon = (8 * Century + 5) \ 25 - 5
Sunday = 5 * Yr \ 4 - LeapDayCorrection - 10
Epact = (11 * Golden + 20 + SynchWithMoon - LeapDayCorrection) Mod 30
If Epact < 0 Then Epact = Epact + 30
If (Epact = 25 And Golden > 11) Or Epact = 24 Then Epact = Epact + 1
N = 44 - Epact
If N < 21 Then N = N + 30
N = N + 7 - ((Sunday + N) Mod 7)
EASTER = DateSerial(Yr, 3, N)
End Function
HTH,
Bernie
MS Excel MVP
C
Chuck
Bernie,
I'm not smart enough to follow what the function is doing. I noticed that in
the VB Editor it doesn't seem to mater if \ of / is used as a division
indicator. I changed it in one place and the code still ran properly. I
thought that possible "SynchWithMoon" might show some relationship to the date
of the full moon before Easter. If it does, I can't discern it.
As a side note, Easter can fall on any Sunday from March 22 to April 25.
Thanks for your help. I can still use your equations to calculate full moons
in relation to the known date of any one full moon.
Chuck
I'm not smart enough to follow what the function is doing. I noticed that in
the VB Editor it doesn't seem to mater if \ of / is used as a division
indicator. I changed it in one place and the code still ran properly. I
thought that possible "SynchWithMoon" might show some relationship to the date
of the full moon before Easter. If it does, I can't discern it.
As a side note, Easter can fall on any Sunday from March 22 to April 25.
Thanks for your help. I can still use your equations to calculate full moons
in relation to the known date of any one full moon.
Chuck
I don't think that the formula calculates anything about the full moon - it is most likely that the
pattern of Easter Sundays fit some other discernable pattern (which I certainly cannot figure out).
But setting one full moon should be able to predict full moons many years into the future or past -
with a slight modification, the code correctly return the moon dates in 1901.
From the same contest that gave that function:
=TRUNC(DATE(J,7,-CODE(MID("NYdQ\JT_LWbOZeR]KU`",MOD(J,19)+1,1)))/7)*7+8
where J is the year...
But this VBA Function may give you a better idea of how the math is done:
Function EASTER(Yr As Integer) As Long
Dim Century As Integer
Dim Sunday As Integer
Dim Epact As Integer
Dim Golden As Integer
Dim LeapDayCorrection As Integer
Dim SynchWithMoon As Integer
Dim N As Integer
Golden = (Yr Mod 19) + 1
Century = Yr \ 100 + 1
LeapDayCorrection = 3 * Century \ 4 - 12
SynchWithMoon = (8 * Century + 5) \ 25 - 5
Sunday = 5 * Yr \ 4 - LeapDayCorrection - 10
Epact = (11 * Golden + 20 + SynchWithMoon - LeapDayCorrection) Mod 30
If Epact < 0 Then Epact = Epact + 30
If (Epact = 25 And Golden > 11) Or Epact = 24 Then Epact = Epact + 1
N = 44 - Epact
If N < 21 Then N = N + 30
N = N + 7 - ((Sunday + N) Mod 7)
EASTER = DateSerial(Yr, 3, N)
End Function
HTH,
Bernie
MS Excel MVP
B
Bernie Deitrick
Chuck,
In VBA, / is regular division, and \ is integer division (the same as using INT(x/y))
Try this:
Msgbox 3/2 & " " 3\2
Changing between the two will not affect the code's ability to run, but it will affect the result.
HTH,
Bernie
MS Excel MVP
I noticed that in
the VB Editor it doesn't seem to mater if \ of / is used as a division
indicator. I changed it in one place and the code still ran properly.
In VBA, / is regular division, and \ is integer division (the same as using INT(x/y))
Try this:
Msgbox 3/2 & " " 3\2
Changing between the two will not affect the code's ability to run, but it will affect the result.
HTH,
Bernie
MS Excel MVP
Chuck said:Bernie,
I'm not smart enough to follow what the function is doing. I noticed that in
the VB Editor it doesn't seem to mater if \ of / is used as a division
indicator. I changed it in one place and the code still ran properly. I
thought that possible "SynchWithMoon" might show some relationship to the date
of the full moon before Easter. If it does, I can't discern it.
As a side note, Easter can fall on any Sunday from March 22 to April 25.
Thanks for your help. I can still use your equations to calculate full moons
in relation to the known date of any one full moon.
Chuck
I don't think that the formula calculates anything about the full moon - it is most likely that
the
pattern of Easter Sundays fit some other discernable pattern (which I certainly cannot figure
out).
But setting one full moon should be able to predict full moons many years into the future or
past -
with a slight modification, the code correctly return the moon dates in 1901.
From the same contest that gave that function:
=TRUNC(DATE(J,7,-CODE(MID("NYdQ\JT_LWbOZeR]KU`",MOD(J,19)+1,1)))/7)*7+8
where J is the year...
But this VBA Function may give you a better idea of how the math is done:
Function EASTER(Yr As Integer) As Long
Dim Century As Integer
Dim Sunday As Integer
Dim Epact As Integer
Dim Golden As Integer
Dim LeapDayCorrection As Integer
Dim SynchWithMoon As Integer
Dim N As Integer
Golden = (Yr Mod 19) + 1
Century = Yr \ 100 + 1
LeapDayCorrection = 3 * Century \ 4 - 12
SynchWithMoon = (8 * Century + 5) \ 25 - 5
Sunday = 5 * Yr \ 4 - LeapDayCorrection - 10
Epact = (11 * Golden + 20 + SynchWithMoon - LeapDayCorrection) Mod 30
If Epact < 0 Then Epact = Epact + 30
If (Epact = 25 And Golden > 11) Or Epact = 24 Then Epact = Epact + 1
N = 44 - Epact
If N < 21 Then N = N + 30
N = N + 7 - ((Sunday + N) Mod 7)
EASTER = DateSerial(Yr, 3, N)
End Function
HTH,
Bernie
MS Excel MVP
Chuck said:Burnie,
I have an equation that accepts an input of a year (2009) and from this single
input it calculates the Month and Day for Easter of that year.
=FLOOR("5/"&DAY(MINUTE(YEAR({year})/38)/2+56)&"/"&YEAR({year}),7)-34
It is not my equation. It is something I found on the internet a fair number
of years ago. Since Easter is the first Sunday after the first full moon on or
after the Vernal Equinox (March 21), the equation some how calculates the date
for at least one full moon. I've tried to 'pull out' that full moon date. But
I'm just not smart enough.. If the date of that full moon can be had from a
single equation, that date can be the reference date to calculate full moons
for the rest of the year using you subroutine.
--
Chuck
Chuck,
There is a page at the Naval Observatory:
http://aa.usno.navy.mil/data/docs/MoonPhase.php#y2009
I just picked two from early this year, and adjusted UTC to EST.
HTH,
Bernie
MS Excel MVP
C
Chuck
Bernie,
Thanks for the education.
Chuck
Thanks for the education.
Chuck
Chuck,
I noticed that in
the VB Editor it doesn't seem to mater if \ of / is used as a division
indicator. I changed it in one place and the code still ran properly.
In VBA, / is regular division, and \ is integer division (the same as using INT(x/y))
Try this:
Msgbox 3/2 & " " 3\2
Changing between the two will not affect the code's ability to run, but it will affect the result.
HTH,
Bernie
MS Excel MVP
Chuck said:Bernie,
I'm not smart enough to follow what the function is doing. I noticed that in
the VB Editor it doesn't seem to mater if \ of / is used as a division
indicator. I changed it in one place and the code still ran properly. I
thought that possible "SynchWithMoon" might show some relationship to the date
of the full moon before Easter. If it does, I can't discern it.
As a side note, Easter can fall on any Sunday from March 22 to April 25.
Thanks for your help. I can still use your equations to calculate full moons
in relation to the known date of any one full moon.
Chuck
I don't think that the formula calculates anything about the full moon - it is most likely that
the
pattern of Easter Sundays fit some other discernable pattern (which I certainly cannot figure
out).
But setting one full moon should be able to predict full moons many years into the future or
past -
with a slight modification, the code correctly return the moon dates in 1901.
From the same contest that gave that function:
=TRUNC(DATE(J,7,-CODE(MID("NYdQ\JT_LWbOZeR]KU`",MOD(J,19)+1,1)))/7)*7+8
where J is the year...
But this VBA Function may give you a better idea of how the math is done:
Function EASTER(Yr As Integer) As Long
Dim Century As Integer
Dim Sunday As Integer
Dim Epact As Integer
Dim Golden As Integer
Dim LeapDayCorrection As Integer
Dim SynchWithMoon As Integer
Dim N As Integer
Golden = (Yr Mod 19) + 1
Century = Yr \ 100 + 1
LeapDayCorrection = 3 * Century \ 4 - 12
SynchWithMoon = (8 * Century + 5) \ 25 - 5
Sunday = 5 * Yr \ 4 - LeapDayCorrection - 10
Epact = (11 * Golden + 20 + SynchWithMoon - LeapDayCorrection) Mod 30
If Epact < 0 Then Epact = Epact + 30
If (Epact = 25 And Golden > 11) Or Epact = 24 Then Epact = Epact + 1
N = 44 - Epact
If N < 21 Then N = N + 30
N = N + 7 - ((Sunday + N) Mod 7)
EASTER = DateSerial(Yr, 3, N)
End Function
HTH,
Bernie
MS Excel MVP
Burnie,
I have an equation that accepts an input of a year (2009) and from this single
input it calculates the Month and Day for Easter of that year.
=FLOOR("5/"&DAY(MINUTE(YEAR({year})/38)/2+56)&"/"&YEAR({year}),7)-34
It is not my equation. It is something I found on the internet a fair number
of years ago. Since Easter is the first Sunday after the first full moon on or
after the Vernal Equinox (March 21), the equation some how calculates the date
for at least one full moon. I've tried to 'pull out' that full moon date. But
I'm just not smart enough.. If the date of that full moon can be had from a
single equation, that date can be the reference date to calculate full moons
for the rest of the year using you subroutine.
--
Chuck
Chuck,
There is a page at the Naval Observatory:
http://aa.usno.navy.mil/data/docs/MoonPhase.php#y2009
I just picked two from early this year, and adjusted UTC to EST.
HTH,
Bernie
MS Excel MVP
B
Bernie Deitrick
Chuck,
You have to remember that the moon travels around the Earth in an ellipse, and that the Earth
travels around the sun in an ellipse as well. Also remember that according to the laws of motion,
the Earth speeds up when it is closer to the sun, and the moon speeds up when it is closer to the
Earth. Also, since the lunar cycle's length is based relative and not sidereal motion, the two can
combine to greatly shorten or lengthen the cycle length. It all averages out, of course - but see
this link:
http://www.obliquity.com/astro/lunarmonth.pdf
HTH,
Bernie
MS Excel MVP
You have to remember that the moon travels around the Earth in an ellipse, and that the Earth
travels around the sun in an ellipse as well. Also remember that according to the laws of motion,
the Earth speeds up when it is closer to the sun, and the moon speeds up when it is closer to the
Earth. Also, since the lunar cycle's length is based relative and not sidereal motion, the two can
combine to greatly shorten or lengthen the cycle length. It all averages out, of course - but see
this link:
http://www.obliquity.com/astro/lunarmonth.pdf
HTH,
Bernie
MS Excel MVP
C
Chuck
Bernie,Is there an equation that will calculate the phases of the moon?
Chuck
If you are still watching the thread, I found a listing of Blue Moons (second
full moon in a month)
Subtracting the first date/time value from the second should give the time for
one lunar cycle. The list has 9 Blue Moons. The longest cycle was 29.73750000
days (10/1/2020 21:05 to 10/31/2020 14:47) GT. The shortest cycle was
29.29583333 days (8/1/2023 18:29 to 8/31/2023 1:35). The difference in cycle
time is 10 hrs 36 mins. That is a huge cycle time variation. Can this
possibly be? No two months had the same cycle time. Makes me wonder about the
accuracy of the source data.
From "Pictorial Astronomy" by Alter, Cleminshaw, and Philips 1966 synodic month
= 29d, 12h, 44m, 2.8s. (29.53058796 days).
Chuck
C
Chuck
On Tue, 31 Mar 2009 09:00:20 -0400, "Bernie Deitrick" <deitbe @ consumer dot
org> wrote:
Bernie,
WOW. I knew we "wobbled" a bit, but I thought it was only of academic
interest. There is no such thing as a fixed moon period. But as you said, it
averages out over about a year. It looks like there are at least 3 'sine'
waves involved, all with a different period and amplitude. Not worth the time
to try to determine the equations for what I'm doing. 29.xxxx is good enough.
Thanks for all your help.
Chuck
org> wrote:
Bernie,
WOW. I knew we "wobbled" a bit, but I thought it was only of academic
interest. There is no such thing as a fixed moon period. But as you said, it
averages out over about a year. It looks like there are at least 3 'sine'
waves involved, all with a different period and amplitude. Not worth the time
to try to determine the equations for what I'm doing. 29.xxxx is good enough.
Thanks for all your help.
Chuck
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.