Need help with a if(and formula with today() please!

Here what I have so far and it works but it gives me an error message and I can't easily add new rows. I'm thinking a small fix will do it.

B c d e f
date #1 date #2 =today() =if(and(D2>=B2,D2<=C2), "east coast", "west coast") =if(and((D2+30)>=B2,(D2+30)<=C2), "east coast", "west coast")


and it continues adding 30 days out to a year where it basically shows a calendar projection of if my people are going to be on the west coast or the east coast.
but it isn't working quite right. Any ideas?

You could use a dynamic that applies to all formulas, instead of manually rewriting it each time:

In E2:
=IF(AND($D2+(30*(COLUMN(A1)-1))>=$B2,$D2+(30*(COLUMN(A1)-1))<=$C2), "east coast", "west coast")

And then copy right across columns

This will translate to =if(and(D2+0>=B2,D2+0<=C2), "east coast", "west coast") in E2, and =if(and(D2+30>=B2,D2+30<=C2), "east coast", "west coast") in F2, incrementing naturally by 30 per cell.

But that only really gives a rough guesstimate on calendar months, so instead we take it one step further using DATE to apply exactly 1 month in advance instead of 30 days (some months are longer than others)

This is your final formula for E2:
=IF(AND(DATE(YEAR($D2),MONTH($D2)+COLUMN(A1)-1,DAY($D2))>=$B2,DATE(YEAR($D2),MONTH($D2)+COLUMN(A1)-1,DAY($D2))<=$C2), "east coast", "west coast")

This will add accurate months, while using the absolute column references indicated by the $ signs. Now you can copy over as far as needed.


calendar projection for seannormand.xlsx