Birthday indicator formula

**Freaky_zoid** · 12-01-2008, 03:13 AM

Hello everyone.

I have a table with employees' persinal data including birthdays. My aim is to insert an indicating field that would show whether the birthday is gonna come soon.

It shold have several states:
number of days left if the birthday is current or next month
"not soon enough" if the birthday will come in more than 2 months (later than next month);
"happy birthday" if it is today;
"the past weekend" if it was the past weekend and it is Monday today.

What can you suggest?

Thanks in advance.

**Bob Phillips** · 12-01-2008, 07:01 AM

Formulae

=IF(birthday_date>TODAY(),IF(birthday_date<=DATE(YEAR(TODAY()),MONTH(TODAY())+2,1),birthday_date-TODAY(),"not soon enough"),IF(birthday_date=TODAY(),"Happ birthday!",IF(birthday_date>=TODAY()-7,"The past week","")))

**Freaky_zoid** · 12-01-2008, 08:00 AM

Hello, Bob.
Thank you for the answer, but this formula doesn't work properly. Most of the cells are blank, I only get results with "happy birthday" and number of days if the birthday is in December. It doesn't count the days correctly when birthday is in January because the month number is 1 and it is less than 12 so [birthday_date<=DATE(YEAR(TODAY()),MONTH(TODAY())+2] doesn't recognize it.

**Bob Phillips** · 12-01-2008, 08:41 AM

The fact that Jan month number is less than Dec month number is irrelevant, the formula works on the date, not the month number.

I just tried a birthday date of 12th Jan 2009 and it returned 42 for me.

If the date could be blank, just pre-prend with

IF(birthday_date="","", ...

**Simon Lloyd** · 12-01-2008, 08:53 AM

Originally Posted by Bob Phillips

I just tried a birthday date of 12th Jan 2009 and it returned 42 for me..

You're 41? still just a puppy!

**Freaky_zoid** · 12-01-2008, 09:19 AM

Bob, I do not have blank cells in the field containing birthdays. I get blank cells when use your formula. but it's not that bad, I have fixed that, I guess.
12th Jan 2009 is not a suitable birthday, I've got dates with years of birth. Try 12th Jan 1969.
I'm trying now to do it using the DATEDIF function, not quite alright yet.

**Simon Lloyd** · 12-01-2008, 09:22 AM

Freaky Zoid, create a dummy workbook with some of the data you are having trouble with and then attach it here using Manage Attachments below the submit button when you post next, that way we will be able to help your further.

**martindwilson** · 12-01-2008, 09:26 AM

=IF(AND(TODAY()+61>A1,A1<>TODAY(),A1-TODAY()>0),A1-TODAY()&"  days to go",IF(TODAY()+61<A1,"not soon enough",IF(A1=TODAY(),"happy birthday",IF(AND(VALUE(TODAY()-A1)<3,OR(WEEKDAY(TODAY(),2)-WEEKDAY(A1,2)=-5,WEEKDAY(TODAY(),2)-WEEKDAY(A1,2)=-6)),"last week end",""))))

this gives more or less 2 months away without complicating the formula too much
now if you want to use actual birthdate then
in b1 put birthdate e.g. 21/12/1972
in a1 use
=VALUE(LEFT(TEXT(B1,"dd-mmm-yyyy"),7)&YEAR(TODAY())) which will return 21/12/2008 for use in above formula (cell formated as date)

**Bob Phillips** · 12-01-2008, 09:35 AM

If you have birth dates as against birthdya dates, other than when the birthday is today, how do you know whether to test the previous birthday or the next birthday?

**Freaky_zoid** · 12-02-2008, 03:06 AM

Martin, thank you, I tried your formula, but it isn't working if birthday is in January. All logical tests returm false and there is "" for false in the last IF function which makes the whole formula return blank field. I guess there should be A1-TODAY()&" days to go".
by the way is it really necessary to use an additional field to make the birthday date this year?
Can I put DATE(YEAR(TODAY(),MONTH(B1),DAY(B1)) everywhere istead of A1?

Bob, I'm afraid I don't unerstand the question

**martindwilson** · 12-02-2008, 05:10 AM

=DATE(YEAR(TODAY()),MONTH(B1),DAY(B1)) is fine
but formula works fine,you must be doing something wrong!(i changed the weekend bit slightly and changed the parameters for >=61 days to get rid of a blank in feb.
see attached

**Bob Phillips** · 12-02-2008, 06:44 AM

Originally Posted by Freaky_zoid

Bob, I'm afraid I don't unerstand the question

I am saying, do I test whether my next birthday is due soon, or whether my last birthday is just past?

**Freaky_zoid** · 12-02-2008, 07:21 AM

Martin, I guess I've got it. You leave it blank if the birthday has passed this year. It shall switch to "not soon enough" when today is 2009.

Bob, this is the same thing. If your birthday has just pass, your next one is not gonna come soon. I don't need to see if the birthday has passed recently

**Bob Phillips** · 12-02-2008, 07:41 AM

Originally Posted by Freaky_zoid

Martin, I guess I've got it. You leave it blank if the birthday has passed this year. It shall switch to "not soon enough" when today is 2009.

Bob, this is the same thing. If your birthday has just pass, your next one is not gonna come soon. I don't need to see if the birthday has passed recently

That is true, but which one do you test, previous or next?

**Freaky_zoid** · 12-02-2008, 08:37 AM

Bob, we check the birthday this year, which can be either previos or next depending on today's date.

I tried the DATEDIF function and here's what I've got:
=IF(ISERR(DATEDIF(TODAY(),DATE(YEAR(TODAY()),MONTH(A1),DAY(A1)),"d")),IF(AND(DATEDIF(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1)),TODAY(),"d")<=2,WEEKDAY(TODAY(),2)=1),"The last weekend",IF(AND(MONTH(A1)=1,MONTH(TODAY())=12),DATEDIF(TODAY(),DATE(YEAR(TODAY())+1,MONTH(A1),DAY(A1)),"d"),"Not soon enough")),IF(DATEDIF(TODAY(),DATE(YEAR(TODAY()),MONTH(A1),DAY(A1)),"d")<61, IF(TODAY()-DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))=0,"Happy birthday",DATEDIF(TODAY(),DATE(YEAR(TODAY()),MONTH(A1),DAY(A1)),"d")),"Not soon enough"))

Looks like working ok, but a bit too long and not easy to work with

**Bob Phillips** · 12-02-2008, 12:12 PM

Originally Posted by Freaky_zoid

Bob, we check the birthday this year, which can be either previos or next depending on today's date.

I tried the DATEDIF function and here's what I've got:
=IF(ISERR(DATEDIF(TODAY(),DATE(YEAR(TODAY()),MONTH(A1),DAY(A1)),"d")),IF(AND(DATEDIF(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1)),TODAY(),"d")<=2,WEEKDAY(TODAY(),2)=1),"The last weekend",IF(AND(MONTH(A1)=1,MONTH(TODAY())=12),DATEDIF(TODAY(),DATE(YEAR(TODAY())+1,MONTH(A1),DAY(A1)),"d"),"Not soon enough")),IF(DATEDIF(TODAY(),DATE(YEAR(TODAY()),MONTH(A1),DAY(A1)),"d")<61, IF(TODAY()-DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))=0,"Happy birthday",DATEDIF(TODAY(),DATE(YEAR(TODAY()),MONTH(A1),DAY(A1)),"d")),"Not soon enough"))

Looks like working ok, but a bit too long and not easy to work with

Persinally, I would calculate the birthday in one cell, and then test against that value, rather than repeat the calculation of the birtday. You can reduce it to

=IF(ISERR(DATEDIF(TODAY(),B1,"d")),
IF(AND(DATEDIF(B1,TODAY(),"d")<=2,WEEKDAY(TODAY(),2)=1),"The last weekend",
IF(AND(MONTH(A1)=1,MONTH(TODAY())=12),DATEDIF(TODAY(),DATE(YEAR(TODAY())+1,MONTH(A1),DAY(A1)),"d"),"Not soon enough")),
IF(DATEDIF(TODAY(),B1,"d")<61, IF(TODAY()-B1=0,"Happy birthday",DATEDIF(TODAY(),B1,"d")),"Not soon enough"))