Birthday reminder formula problem

**Telegraph Sam** · 12-28-2015, 10:42 PM

I am getting blocked by this simple problem in a worksheet under construction with the objective to give a countdown to my relatives' birthday dates.

I want a formula to output for example "10" (as a positive number) when the birthday was on 5th May 2009 and the current date is (say) 25 April 2015. The difficulty comes when I want the formula to activate no more than 30 days in advance. I have been trying all combinations of the usual stacked if(), month() and day() functions but to no avail. Can anyone help? Tks

**Beamernsw** · 12-28-2015, 11:33 PM

With the date in question in Cell A1:

=IF(AND(A1-TODAY()<31,A1-TODAY()>0),A1-TODAY(),"")

**Telegraph Sam** · 12-29-2015, 06:29 PM

Thanks, it looks like I couldn't see the wood for the trees (common experience). I will insert your solution into my "environment" and report back on the result ..

**Pete_UK** · 12-29-2015, 08:04 PM

If A1 contains the date of birth, then you will need to change the date to this year within the formula, i.e.:

DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))

instead of just A1.

Hope this helps.

Pete

**Beamernsw** · 12-29-2015, 11:47 PM

Good call Pete, thank you for picking up that mistake.

**Telegraph Sam** · 12-30-2015, 05:49 PM

Thanks for both contributions but I'm afraid that I am still a lap behind you...

In my spreadsheet cell C1 contains today's date i.e. =today(). For the sake of debugging I have temporarily / arbitrarily changed it to 18 Oct 2015
The date of birth is set at 29 Oct 2009, in cell B7
The "remaining time alert" is in cell D7 and this is the formula that I ended up with which half works
=IF(MONTH(B7)=MONTH($C$1),IF(DAY($C$1)<=DAY(B7),DAY(B7)-DAY($C$1),""))
It seems to work within the month in question (Oct) when the current day-date is approaching the birth-day-date, as above. But it fails if for example the original birthday was 5th Sept 2009 and the current date is 29th August 2015 (for example). In other words when I am asking it to "bridge" two successive months.
I have been trying so far without huge success to incorporate your suggestions into mine, or alternatively to combine and substitute them altogether! I fear that some additional help will be necessary to get me over the hump please.

**Pete_UK** · 12-30-2015, 06:00 PM

Okay, put this in D7:

=IF(AND(DATE(YEAR($C$1),MONTH(B7),DAY(B7))-$C$1<31,DATE(YEAR($C$1),MONTH(B7),DAY(B7))-$C$1>0),DATE(YEAR($C$1),MONTH(B7),DAY(B7))-$C$1,"")

This is a combination of Beamer's formula with my correction, adjusted to suit your data layout, and should cope with all date ranges.

Hope this helps.

Pete

**Telegraph Sam** · 12-30-2015, 06:25 PM

Hi Pete

Great that seems to be the fix at last many thanks. I doubt if I could have got there myself unaided. Long live the Excel Help forum!

Iain

**Pete_UK** · 12-30-2015, 06:43 PM

You're welcome, Iain - thanks for feeding back.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

**Telegraph Sam** · 12-30-2015, 06:51 PM

Have done ..

**Telegraph Sam** · 12-31-2015, 09:48 PM

And one day I'll sus out the logic behind the formula in its latest form!

**Aardigspook** · 01-01-2016, 05:18 AM

Here's the logic:
If the date calculated by (today's year, month of birthday, day of birthday) is less than 31 days away and more than 0 days away, then subtract today's date from that date to give a number, otherwise return blank ("").

There are only two problems with this formula - one minor, one major. The minor one is that when you get to the birthday being today, it returns blank - you might want it to actually tell you that the birthday is today

. The major one is that if the birthday is in January and today is December, it doesn't work as the years don't match.

The solution to the first is quite simple - first check if the calculated date minus today's date equals 0, like this:

Formula:

=IF(DATE(YEAR($C$1),MONTH(B5),DAY(B5))-$C$1=0,"Birthday!",IF(AND(DATE(YEAR($C$1),MONTH(B5),DAY(B5))-$C$1<31,DATE(YEAR($C$1),MONTH(B5),DAY(B5))-$C$1>0),DATE(YEAR($C$1),MONTH(B5),DAY(B5))-$C$1,""))

The solution to the second is to insert another IF to check if 'today' ($C$1) is in month 12 (december) and if so to add a year to that date in the calculations - giving us the final formula of:

Formula:

=IF(DATE(YEAR($C$1),MONTH(B5),DAY(B5))-$C$1=0,"Birthday!",IF(AND(MONTH($C$1)=12,DATE(YEAR($C$1)+1,MONTH(B5),DAY(B5))-$C$1<31,DATE(YEAR($C$1)+1,MONTH(B5),DAY(B5))-$C$1>0),DATE(YEAR($C$1)+1,MONTH(B5),DAY(B5))-$C$1,IF(AND(DATE(YEAR($C$1),MONTH(B5),DAY(B5))-$C$1<31,DATE(YEAR($C$1),MONTH(B5),DAY(B5))-$C$1>0),DATE(YEAR($C$1),MONTH(B5),DAY(B5))-$C$1,"")))

The logic of the final formula is:
If the date calculated by (today's year, month of birthday, day of birthday) is the same as today's date, return 'Birthday!'. That's this bit:

Formula:

IF(DATE(YEAR($C$1),MONTH(B5),DAY(B5))-$C$1=0,"Birthday!",

If today's date is in December and (next year, month and day of birthday) is <31 and >0 days away, subtract today's date from that date to give the number of days remaining. That's this next bit:

Formula:

IF(AND(MONTH($C$1)=12,DATE(YEAR($C$1)+1,MONTH(B5),DAY(B5))-$C$1<31,DATE(YEAR($C$1)+1,MONTH(B5),DAY(B5))-$C$1>0),DATE(YEAR($C$1)+1,MONTH(B5),DAY(B5))-$C$1,

If (today's year, month and day of birthday) is <31 and >0 days away, subtract today's date from that date to give the number of days remaining. That's the (almost) last bit:

Formula:

IF(AND(DATE(YEAR($C$1),MONTH(B5),DAY(B5))-$C$1<31,DATE(YEAR($C$1),MONTH(B5),DAY(B5))-$C$1>0),DATE(YEAR($C$1),MONTH(B5),DAY(B5))-$C$1,

Finally, if none of the above are true, return blank, then close off the formula with three brackets because there are three IF statements:

Formula:

"")))

I hope the final formula is useful and that the explanation helps a bit.

Happy New Year!

Edit:
To check for today = birthday, you could use this instead which just checks that the day and month of the birthday are the same as the day and month today:

Formula:

IF(AND(DAY($C$1)=DAY(B5),MONTH($C$1)=MONTH(B5)),"Birthday!",

which would give you a final formula of:

Formula:

=IF(AND(DAY($C$1)=DAY(B5),MONTH($C$1)=MONTH(B5)),"Birthday!",IF(AND(MONTH($C$1)=12,DATE(YEAR($C$1)+1,MONTH(B5),DAY(B5))-$C$1<31,DATE(YEAR($C$1)+1,MONTH(B5),DAY(B5))-$C$1>0),DATE(YEAR($C$1)+1,MONTH(B5),DAY(B5))-$C$1,IF(AND(DATE(YEAR($C$1),MONTH(B5),DAY(B5))-$C$1<31,DATE(YEAR($C$1),MONTH(B5),DAY(B5))-$C$1>0),DATE(YEAR($C$1),MONTH(B5),DAY(B5))-$C$1,"")))

There's no difference in the result.

Lastly, you could replace all the $C$1 references with TODAY() (for example YEAR($C$1) would become YEAR(TODAY()) instead) if you wanted to get rid of the cell containing today's date. The easiest way to do this would be to use Find and Replace. But personally, I'd probably leave it there!

**Telegraph Sam** · 01-01-2016, 09:08 AM

Many thanks for your work over Hogmanay - not expected. It gets quite complex so I want to limit myself for now to solving the "minor" problem. I agree that unlike with my original formula the latest revision does not "show" when the current day-date is the same as the birthday-day-date which is the intention. The problem is that when I cut & paste your formulas into my spreadsheet I am getting just "#VALUE!" Something ain't quite right yet and I fear that it will take more than 5 minutes debugging to sus out just what ..

**Pete_UK** · 01-01-2016, 09:13 AM

Aardigspook's formulae referred to a date in cell B5, not in B7, so the easiest thing to do is to copy them into D5 (where you might still get an error if B5 does not contain a date of birth), and then copy into D7.

Happy New Year.

Hope this helps.

Pete

**Aardigspook** · 01-01-2016, 09:31 AM

Originally Posted by Telegraph Sam

Many thanks for your work over Hogmanay - not expected

You're welcome - I'm meant to be doing actual work, but doing a bit of procrastinating...

Originally Posted by Pete_UK

Aardigspook's formulae referred to a date in cell B5, not in B7

Oops

I started off with a date in B7, but then during testing extended up and down a bit, so ended up referring to B5 - sorry!

Here are the two options with the correct ref:

Formula:

=IF(DATE(YEAR($C$1),MONTH(B7),DAY(B7))-$C$1=0,"Birthday!",IF(AND(MONTH($C$1)=12,DATE(YEAR($C$1)+1,MONTH(B7),DAY(B7))-$C$1<31,DATE(YEAR($C$1)+1,MONTH(B7),DAY(B7))-$C$1>0),DATE(YEAR($C$1)+1,MONTH(B7),DAY(B7))-$C$1,IF(AND(DATE(YEAR($C$1),MONTH(B7),DAY(B7))-$C$1<31,DATE(YEAR($C$1),MONTH(B7),DAY(B7))-$C$1>0),DATE(YEAR($C$1),MONTH(B7),DAY(B7))-$C$1,"")))

or

=IF(AND(DAY($C$1)=DAY(B7),MONTH($C$1)=MONTH(B7)),"Birthday!",IF(AND(MONTH($C$1)=12,DATE(YEAR($C$1)+1,MONTH(B7),DAY(B7))-$C$1<31,DATE(YEAR($C$1)+1,MONTH(B7),DAY(B7))-$C$1>0),DATE(YEAR($C$1)+1,MONTH(B7),DAY(B7))-$C$1,IF(AND(DATE(YEAR($C$1),MONTH(B7),DAY(B7))-$C$1<31,DATE(YEAR($C$1),MONTH(B7),DAY(B7))-$C$1>0),DATE(YEAR($C$1),MONTH(B7),DAY(B7))-$C$1,"")))

**Telegraph Sam** · 01-01-2016, 09:22 AM

Yup, the thought occurred that it might be a case of me simply getting the cell refs wrong in my spreadsheet and that was / is going to be my first line of investigation once the effects of last night have worn off. Tks.

**Telegraph Sam** · 01-01-2016, 10:21 AM

Thanks that seems to have done the trick - I have just substituted 0 in place of "Birthday!" to get it to fit in with my spreadsheet format. Unless of course it turns out that I do need the more complicated change-year formula at which point the process gets repeated!

A good start to 2016 and a HNY to both of you

PS I have had to log in 3 x in order to send this reply so don't know if my previous credits got passed through or not.

**Pete_UK** · 01-01-2016, 10:28 AM

Yes, thanks for the rep (both of them).

Pete

**Aardigspook** · 01-01-2016, 10:36 AM

Glad you got it working, thanks for the rep (x 2) and a Good New Year to you too.

Birthday reminder formula problem

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Birthday reminder formula problem

Re: Birthday reminder formula problem

Re: Birthday reminder formula problem

Re: Birthday reminder formula problem

Re: Birthday reminder formula problem

Re: Birthday reminder formula problem

Re: Birthday reminder formula problem

Re: Birthday reminder formula problem

Re: Birthday reminder formula problem

Re: Birthday reminder formula problem

Re: Birthday reminder formula problem

Re: Birthday reminder formula problem

Re: Birthday reminder formula problem

Re: Birthday reminder formula problem

Re: Birthday reminder formula problem

Re: Birthday reminder formula problem

Re: Birthday reminder formula problem

Re: Birthday reminder formula problem

Re: Birthday reminder formula problem

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Automatic Email or Reminder When Birthday is Approaching

Macro to send birthday reminder via outlook email

Birthday and work anniversary reminder from excel file to outlook email

Birthday reminder

Birthday Problem Simulation

wish to make a Birthday reminder!!

Getting problem in reminder for assigned tasks

Bookmarks

Bookmarks

Posting Permissions