Trouble with dates on perpetual (moon) calendar I'm working on

[happiwolf]

So Moons.B3 = 01/11/2024
Moons.B4 = 02/09/2024

These are dates of the new moon each month.

I have a calendar that displays 7 day per row, but based on the moon not julian calendar

The first week starts B4, ends H4 (Jan 11, Jan 12, Jan 13…Jan 17)
Second week starts B5, ends H5 etc

I used this function to check the new moon date, and if the current date is less than the new moon date, to write in the next day in the cell, otherwise do nothing

=IF(DATEVALUE(B4)<Moons.$B4,TEXT(B4+1,“MMM DD”),"")
Shows Jan 12

=IF(DATEVALUE(C8)<Moons.$B4,TEXT(C8+1,“MMM DD”),"")
Show Feb 9

C8 is Feb 9, which is the next new moon, my intention was that it should be blank, but instead it displays “Feb 9”, and every cell after that also shows a date, when it should be blank

lbo 7.6.4.1 windows

[Wanderer]

Difficult without your actual sheet.
.
Typical problems are dates not recognized but interpreted as text and problems with locale and date acceptance patterns.
.
Did you check the actual values of the cells, maybe changing the format of the cell to integer?

[happiwolf]

Ok. I think I know what’s happening. It thinks the cell values for the month are thinking it’s 2023, and the date values for the other sheet are 2024.

When I’m filling the month cells I’m using =TEXT(Moons.$B3,“MMM DD”) for the first cell, then converting it back to a date. So it loses that the year is 2024, and assumes 2023, because that’s the current year now.

I realized I could change the format of the cell to be MMM DD, and do =Moons.$B3 so it still shows the “Jan 11”. But it still doesn’t work because the next cell over takes Jan 11 + 1 and it’s the same issue.

It seems as long as I’m not listing the year in the cell it assumes the current year and not the future year that I’m working with.

I’m not sure at this point how to make them know they’re both in 2024.

[EarnestAl]

Is there a benefit in converting to text and back again? Why not just =IF(B4<Moons.$B4-1,B4+1,"")? I did the -1 to ensure a blank on the day of the new moon, new month on a new row.