CODEWITHSUNDEEP
google-sheets
smarsmarsmar
smarsmarsmar

Reputation: 1

Google spreadsheet: birthday reminder via conditional formatting

Please, help to do conditional formatting to highlight birthdays (which are written as MM/DD/YYYY) in Google Spreadsheet. Should be 3 conditions:

  1. to highlight a birthday we used formula

=and(month(B1:B1000)=month(today()),day(B1:B1000)=day(today()))

which works

  1. to highlight a day before birthday we used formula

=and(month(B1:B1000)=month(today()),day(B1:B1000)=day(today()-1))

but this formula don't work in cases when birthday day is 1st of month

  1. need to highlight date in case between seven days before birthday and 1 day before birthday

Upvotes: 0

Views: 1445

Answers (1)

Erik Tyler
Erik Tyler

Reputation: 9355

Here are some rules (assuming the custom CF formulas are being applied to the range B1:B1000:

Birthday is today

=DATEVALUE(TEXT(B1,"m/d"))=TODAY()

Birthday is tomorrow

=TEXT(B1-1,"m/d")=TEXT(TODAY(),"m/d")

Birthday is between 2 and 7 days from today

=(DATEVALUE(TEXT(B1,"m/d"))>(TODAY()+1))*((DATEVALUE(TEXT(B1,"m/d"))-7)<=TODAY())

The first two (for today or tomorrow) are simpler than what you were using.

The third will flag, as I've said, if the birthday is in the next 2 - 7 days. Your post says that you want between 1 and 7 days in this rule; but that would overlap with your "tomorrow" rule.

Rules must account not only birthday ranges that fall between months, but those that fall between years (i.e., flagging a Jan 1 birthday when it is December 31 of the previous year), which is why the final rule perhaps seems a bit long.

Upvotes: 1

Related Questions