Wrap around year from December to January

Question

I have the following formula set up to determine if someone's birthday is coming up within the next week.

=IF(AND(Q2>(TODAY()+4),Q2<(TODAY()+12)),"Yes","No")

*Note: Q2 is the person's birthday, but in the year 2016. (ie. 12/20/1978 is actually 12/20/2016)

This works, except for when I get to roughly the middle of December as the next week will spill over into next year. How can I get the range of next week to wrap around into January of the next year (ie. from December 2016 to January 2017)?

Answer 1

You can put the date in the current year with a formula like

=date(2016,month(q2),day(q2))

To take into account that the date plus a few days can be in the next year, you can use

=date(year(today()+ajustment),month(q2),day(q2))

Your whole formula then looks like this:

=IF(AND(DATE(YEAR(TODAY()+4),MONTH(Q13),DAY(Q13))>(TODAY()+4),DATE(YEAR(TODAY()+12),MONTH(Q13),DAY(Q13))<(TODAY()+12)),"Yes","No")

Now the formula works for dates in the new year, too.

Answer 2

The problem is that your solution is dependent on year, when you should only be screening on the month and day. This could be accomplished by using the TEXT(...) function.

For example: =VALUE(TEXT(I2,"mm"))

will return the month as a number. Is this close to what you were looking for?

Here is how the solution would look using this approach:

=IF(AND(VALUE(TEXT(G2,"mm"))=VALUE(TEXT(TODAY(),"mm")),VALUE(TEXT(G2,"dd"))>(VALUE(TEXT(TODAY(),"dd"))+1),VALUE(TEXT(G2,"dd"))<(VALUE(TEXT(TODAY(),"dd"))+12)),"Yes","No")

Also, this solution is year-independent, so you could use someone's actual birthday and not have to change the year every time.

Answer 3

You can use DATEDIF to get the difference in year, month or days, as specified, between two dates. For instance:

=DATEDIF("start_date", "end_date", "d")

=DATEDIF("1/11/2011", "10/11/2011", "d") Result: 9

Scratch:

Q3 = DATEDIF(today, birthday date,"d")

=IF(AND(Q3>4,Q3<12),"Yes","No")

So you can use DateDiff inside your IF statement to get birthday assessment in next days. It is worth noting that you are not get the anniversary from the next week with this kind of formula, just getting those which birthday date occurs between 4 days of today and less than 12 days from today.