Calcule the phase of the moon based on the date

Question

I'm working on a project and need to associate the phase of the moon with each date I have. I would like to have 1= new moon, 2= first quarter, 3= full moon and 4= last quarter. I work in Excel and I tried several times before asking but the results were wrong if I check some of my results. Can you help me? I have 1500 observations and it is not possible for me to write them by hand.

I use this:=MOD(INT((B2-DATE(2000;1;6))/27,3);4)+1 B2 is my date and January 6, 2000 was the new moon. The formula subtracts the known new moon date and divides by the average lunar month length, taking the integer part. Then, it takes the remainder when dividing by 4 and adds 1 to get the moon phase (1 for new moon, 2 for first quarter, 3 for full moon, and 4 for last quarter). I also tried with 29.53 for the lunar month because on the web I could find both options for the lunar month.

calendar explaining phases of the moon in relation to dates

Answer 1

This one will work, with latest new moon as reference:

=INT((MOD((B2-DATE(2024;1;11));29.53059))/29.53059*4)+1

Success