Birthday reminder formula problem

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

With the date in question in Cell A1:

Please Login or Register  to view this content.

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 ..

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

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

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.

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

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

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

Have done ..

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

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 ..

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

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.

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.

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

Pete