Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Excel Birthdays 1
- Thread starter Evil6
- Start date
bluedragon2
IS-IT--Management
=YEAR(B1)-YEAR(A1)+1
[Blue]Blue[/Blue] [Dragon]
If I wasn't Blue, I would just be a Dragon...
[Blue]Blue[/Blue] [Dragon]
If I wasn't Blue, I would just be a Dragon...
bluedragon2
IS-IT--Management
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...
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...
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............
----------------------------------------------------------------------------
![[peace]](/data/assets/smilies/peace.gif "[peace] [peace]")
It's easier to beg forgiveness than ask permission![[2thumbsup]](/data/assets/smilies/2thumbsup.gif "[2thumbsup] [2thumbsup]")
----------------------------------------------------------------------------
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
IS-IT--Management
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...
[Blue]Blue[/Blue] [Dragon]
If I wasn't Blue, I would just be a Dragon...
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)))
=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)))
-
1
- #7
TonyJollans
Programmer
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.
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.
- Thread starter
- #8
- Thread starter
- #9
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.
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
Programmer
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.
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.
- Thread starter
- #11
TonyJollans
Programmer
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.
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.
- Thread starter
- #13
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
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
Programmer
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.
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.
- Thread starter
- #15
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.
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
Programmer
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.
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.
- Thread starter
- #17
TonyJollans
Programmer
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.
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.
- Thread starter
- #19
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.
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
Programmer
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.
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.
- Status