DATE OF BIRTH (FORMULA FOR NEXT BIRTHDAY WHETHER IN 2013 OR 2014)

[BermondseyBoy]

Hi There

Could someone please have a look at my uploaded file and kindly have a look at a formula I need to work out for a birthday reminder worksheet I have set up from Chandoo's fantastic 'birthday reminder' tutorial on YouTube. It all works absolutely fine for except when I have put in the formula below in cell C9 to calculate the next birthday working from column E, cell E9 which is for their Dates of Birth it's only calculating the next birthday for each individual only in year 2013 and not in year 2014 if the individual has already had their respective birthday in year 2013, can someone please tell me as to where I have gone wrong and what I need to add to the formula below to make it work out when in year 2014 the birthdays will be if the individual has already had their birthday in year 2013 as previously stated above?

=DATE(YEAR(TODAY()),MONTH(C9),DAY(C9))

Thanks so much for your help as its driving me crazy not being able to figure out the correct formula.

Kind Regards

Mickey Shone

 

[NARAYANK991]

Hi Mickey ,

Check your file now.

Narayan

 

[BermondseyBoy]

Hi Narayan

Thank you so much for your kind help.

Kind Regards

Mickey (Maidstone in the UK)

 

[Hui]

=DATE(IF(DATE(0,MONTH(C9),DAY(C9))<DATE(0,MONTH(TODAY()), DAY(TODAY())), YEAR(TODAY()), YEAR(TODAY())+1), MONTH(C9), DAY(C9))

 

[Sajan]

One more approach:
=(TEXT(A1,"m/d/")&(YEAR(TODAY())+(((TEXT(A1,"m/d/")&YEAR(TODAY()))+0)<TODAY())))+0

where A1 contains the birthday date. (The year for the date in A1 does not matter.)

 

[SirJB7]

Hi, BermondseyBoy!
Very late but just arriving here from other link, so just for the records here it goes other variant:
=FECHA(AÑO(HOY())+(HOY()>FECHA(AÑO(HOY());MES(A1);DIA(A1)));MES(A1);DIA(A1)) -----> in english: =DATE(YEAR(TODAY())+(TODAY()>DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))),MONTH(A1),DAY(A1))
Regards!

 

[shrivallabha]

=TEXT(A1,"m/d/")&(YEAR(TODAY())+((TODAY()-A1)>0))+0

 

[SirJB7]

@shrivallabha
Hi!
Like Sajan's previous formula they need to be adjusted for regional settings configuration (if other than m/d/y as off USA, and I don't know if other places too), and they both retrieve text values instead of date values.
I didn't check Sajan's one but with your's I don't know if it's because of my d/m/y settings but I tried it with a date after current month & day (e.g., 25/Dec/2000) and it returns a date for year 2014 instead of 2013. Could you please check it? Thank you.
Regards!

EDITED

PS: Missing file.
https://dl.dropboxusercontent.com/u...2014) (for BermondseyBoy at chandoo.org).xlsx

 

[shrivallabha]

SirJB7,

The setting here is m/d/y but I've seen some text using function getting results twisted up.What language settings do you use? I just changed them to Spanish (Argentina) and the function returned Text result. So you are spot on with it. A numeric formula would be fail safe option.

That also changed formula argument separator to ";" from ",". So I'd assume experienced pros like you'd know what would be changed when such formula is posted.

For some reason I had to add brackets and then add zero to force a numeric result in d/m/y version.
=(TEXT(A1;"d/m/")&(YEAR(TODAY())+((TODAY()-A1)>0)))+0

 

[SirJB7]

@shrivallabha
Hi!
Yes, my settings are Spanish Argentine.
And yes, I was aware of that change , so in my posted file I wrote the function using the correct separator (semicolon for me, that you might have read as comma, I hope).
And yes again, the embrace worked for converting to number, it seems as the numeric operator "+" has a higher priority than the string operator "&".
But I think that the 2013/2014 year issue is off this considerations, isn't it?
Regards!