Moon Dates in excel

[callsidd]

Hi,

Can anyone please guide how I can get moon dates in excel? Means whatever date I input, result show moon phase date/age?



Regards..

 

[Johnny C]

You need to calculate the Paschal Full Moon date either side of the date, and interpolate how far between the two the date is.

To calculate PFM:

Public Function PFM(InputDate as Date) as Date
 
Dim FirstDig, Remain19, temp    'intermediate results    
Dim PFM as date
 
FirstDig = int(InputDate/100) 'first 2 digits of year
Remain19 = InputDate Mod 19             'remainder of year / 19
 
' calculate PFM date
temp = (FirstDig - 15) \ 2 + 202 - 11 * Remain19
 
   Select Case FirstDig
      Case 21, 24, 25, 27 To 32, 34, 35, 38
         temp = temp - 1
      Case 33, 36, 37, 39, 40
         temp = temp - 2
   End Select
   temp = temp Mod 30
 
   PFM = temp + 21
   If temp = 29 Then PFM = PFM - 1
   If (temp = 28 And Remain19 > 10) Then PFM = PFM - 1
 
End Function

 

[Johnny C]

Editted: I misread the post and posted something for finding the fullm Moon before Easter.

What you need to do is find the date of a full moon (such as Saturday's). Subtract the days bwtween the date you want and that date. If you can work out the time of the fullmoon on Sat, put that into the date as well

Public Function MoonAge(InputDate As Date) As Date
 
     startDate = #3/18/2011#
     MoonAge = ((InputDate - startDate) Mod 29.5305882) / 29.5305882
 
End Function
 
 
Public Function MoonPhase(InputDate As Date) As String
 
     startDate = #3/18/2011#
 
Dim moonage%
Dim Moont
 
     Moont = (((InputDate - startDate) Mod 29.5305882) / 29.5305882) * 8
     moonage = Int(Moont)
 
     Select Case moonage
     Case 0
         MoonPhase = "New Moon"
     Case 1
         MoonPhase = "Waxing Crescent Moon"
     Case 2
         MoonPhase = "Quarter Moon"
     Case 3
         MoonPhase = "Waxing Gibbous Moon"
     Case 4
         MoonPhase = "Full Moon"
     Case 5
         MoonPhase = "Waning Gibbous Moon"
     Case 6
         MoonPhase = "Last Quarter Moon"
     Case 7
         MoonPhase = "Waning Crescent Moon"
     Case Else
         MoonPhase = "Error - Moon has plunged into the Earth"
     End Select
 
End Function

 

[shg]

This gives results within a half day or so from the US Naval Observatory numbers at
http://www.usno.navy.mil/USNO/astronomical-applications/data-services/phases-moon. New moon is zero, 0.5 is full moon.

Public Function MoonAge(ByVal tDate As Date) As Double
    Const a         As Double = 0.000000000102026
    Const b         As Double = 29.530588861
    Const k         As Double = 20.362955
    
    Dim NA          As Double   ' full moon number prior to tDate
    Dim NB          As Double   ' full moon number subsequent to tDate
    Dim c           As Double
    Dim tFMA        As Date
    Dim tFMB        As Date
 
    c = k - tDate + #1/1/2000#

    NA = Int((-b + Sqr(b ^ 2 - 4 * a * c)) / (2 * a))
    NB = NA + 1
 
    tFMA = a * NA ^ 2 + b * NA + k + #1/1/2000#
    tFMB = a * NB ^ 2 + b * NB + k + #1/1/2000#
 
    MoonAge = (tDate - tFMA) / (tFMB - tFMA)
    MoonAge = MoonAge + IIf(MoonAge <= 0.5, 0.5, -0.5)
End Function