Excel Birthdays 1

[Evil6]

Hello.

I wonder if you can help me. I'm trying to make excel compare a person's birthdate (dd-mm-yyyy) in one cell with another cell with the function NOW, so it will give me that person's next birthday on another cell. Is that possible?

Thank you.

 

[bluedragon2]

=YEAR(B1)-YEAR(A1)+1



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

Should have exponded:

cell B1 has the NOW()
cell A1 has the birthdate



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[KenWright]

Hi Blue - Won't that give the same answer regardless of whether NOW() or TODAY() even is either side of the birthdate as long as both dates are in the same year.

Assuming I'm not missing a trick here, then comparing Months and days to see which date, ie day month and year the next birthday will fall on:-

With Birthday in cell A1

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

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[bluedragon2]

I wasn't quite clear on the question, some I jsut threw up a quickie that will return the persons age on their next birthday.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[ETID]

And if you want to count the B-day if it's today...change the >0 to >=0...counts the next birthday date as "today" if B-day is exactly one year ago today...

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

 

[TonyJollans]

Hi Evil6,

Slightly shorter is ..

[blue][tt] =DATE(YEAR(A1)+DATEDIF(A1,NOW(),"y")+1,MONTH(A1),DAY(A1))[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[Evil6]

Wow, that was quick. I wasn't expectinga any messages untill tomorrow or so. Thank You, I'll try it and let you know.

 

[Evil6]

I'm sorry, maybe I didn't make myself very clear. The purpose of this is to alert the user of somone's birthday on the day.
For example, I want to be able to open the excel file and immediatly be alerted that someone is celebrating his/her birthday today.

Besides, I've tested all the formulas that you've provided me and with the exception of Bludragon2's, I can't seem to get them to work... is date formatting needed? As I said, the date is being introduced in the format dd-mm-yyyy.

Once again thanks.

 

[TonyJollans]

Hi Evil6,

Yes, Date formatting will be needed for all the formulae.

To check if the birthday is today, given a D.O.B. in A1, there are several ways, here's one ..

[blue][tt]=IF(DATEDIF(A1,TODAY(),"yd")=0,"Today's the Day!","")[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[Evil6]

Hi Tony,

Thanks for your reply. And what formatting do you refer to?
Can't it be dd-mm-yyyy?

 

[TonyJollans]

Hi Evil6,

I think we may be talking at cross purposes here. If you have a Date in A1 (not a Text string that looks like a date) then you should be OK. What was it that didn't work?

I'm not sure, now, whether you're sorted or not. Let us know.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[Evil6]

Hi Tony.

Ok, as you might have figured it out by now, I'm really grasping at straws here...
Ok, the thing is that I have an application that holds personnal data. I take from that application a delimited text file wich I convert to excel format. Then, I have a person's name, address, ..., and date of birth. So, what I wanted to do is take the information in that particular cell and use it to be informed if someone on that list is celebrating his/her birthday today. I hope that clarifies things for you. I'm sorry I didn't explained myself better.

Thanks

 

[TonyJollans]

Hi Evil6,

I'm still not sure if you're sorted or not! So ..

Values in Cells may be (amongst other types) Text or Dates. Text is exactly that; Date is a special type (actually it's a number interpreted in a special way and formatted as a Date). If you are using Excel to import your data I would expect it to be clever enough to realise you had dates and treat them specially but there are several situations where it might 'fail'.

Let's say you have your DOB as a Date in Cell [blue]A1[/blue]. The formula I gave you earlier should indicate whether today is the birthday or not. To recap ..

[blue][tt]=IF(DATEDIF(A1,TODAY(),"yd")=0,"Today's the Day!","") [/tt][/blue]

.. this says "if the difference between the date and today (measured in days of the year) is zero, then display the message; otherwise display blank".

Now if, instead, the DOB is Text then it must first be converted to a date before it can be used in Date Functions such as DATEDIF. If so, use this version of the formula ..

[blue][tt]=IF(DATEDIF([/tt][red][tt]DATEVALUE([/tt][/red][tt]A1[/tt][red][tt])[/tt][/red][tt],TODAY(),"yd")=0,"Today's the Day!","") [/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[Evil6]

Hi Tony. Thanks for the explanation.

Ok, so I've figured it out that the file exports the date to text. But as I test this last formula you have provided, I get he excel message that "The formula you typed contains an error" and it highlights me this part: ",TODAY". I'm testing it on cell B1 with the date/text on A1.
Am I doing something wrong?

Thank you.

 

[TonyJollans]

Hi Evil6,

It sounds like you have a typo. The formula is syntactically correct, whether or not it does what you want. Make sure you get it exactly right by cutting and pasting the formula from the post.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[Evil6]

Hi Tony.

That's exctly what I did, copy and paste. Does it make any difference the excel version? I'm using Excel 2002 SP3.
I wish that there was some way I could post here a print screen for you to see the error...

 

[TonyJollans]

Hi Evil6,

I am running Excel 2K. As far as I know, the Excel version shouldn't make a difference but DATEDIF does have one or two quirks and might differ from release to release.

If you have access to any webspace anywhere where you can put an image, you can link to it from here by using the IMG TGML tag (click on the Process TGML link under the box where you type your post for full details). Alternatively you can send me either an image or a workbook at [blue]T o n y @ J o l l a n s . c o m[/blue] (remove the spaces!)

A couple of things you can try.

(1) Use NOW() instead of TODAY()

(2) An alternative formula ..

[blue][tt]=IF(DATE(YEAR(TODAY()),MONTH(DATEVALUE(A1)),DAY(DATEVALUE(A1)))=TODAY(),"Today's the day!","")[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[Evil6]

Tony,

Thank you for your patience, I know I've been a nag to you these last few days. I've sent you my test excel sheet, so you can see if I'm doing anything wrong. I've put all the 3 formulas you've provided, for you to see that I've done the copy/past thing you've told me to. Still, it gives me errors. The last formula you gave me, it jams on the ",MONTH" part. But you can see that on the print screens that I send in the file. Using now also didn't work.

Thank you very much.

 

[TonyJollans]

Hi Evil6,

Thanks for the e-mail, including the screen shots - I wouldn't have got it (assuming I have) without them.

The screenshot shows a prompt for the function arguments - handy feature, that (new in 2K2 I assume) - [purple]DATE(year; month; day)[/purple] - with semicolons separating the arguments instead of commas.

Your e-mail address is, I think, Portuguese so I'm guessing that you have a comma as the decimal symbol in your Regional settings. When this is the case I understand that the semi-colon is used as the argument separator in function calls, so try ..

[blue][tt]=IF(DATEDIF(DATEVALUE(A1)[red][highlight];[/highlight][/red]TODAY()[red][highlight];[/highlight][/red]"yd")=0[red][highlight];[/highlight][/red]"Today's the Day!"[red][highlight];[/highlight][/red]"")[/tt][/blue]

.. or ..

[blue][tt]=IF(DATE(YEAR(TODAY())[red][highlight];[/highlight][/red]MONTH(DATEVALUE(A1))[red][highlight];[/highlight][/red]DAY(DATEVALUE(A1)))=TODAY()[red][highlight];[/highlight][/red]"Today's the day!"[red][highlight];[/highlight][/red]"")[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.