• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Finding DOB

K

keanur

Member
Dear Excel ninjas,


first of all, i just rejoined the community as i lost my previous password. here is i have table extracting from system and it contains No, Name and DOB and it is a big data as sometimes we extracted with a long date of range ( 1950 - 1995). i was trying to find specific DOB for every database that i have, so for example if today is 02 July 2013, then i want to search how many persons are having birthday on 2nd July, regardless the year. i have tried to use Vlookup or sort function from excel but still not helping me. Do you have any suggestion on this. Oh, btw below is the sample file.


skydrive: https://skydrive.live.com/redir?resid=B74F44A51EEC822B!183&authkey=!AMpq--ynkIB8nLw


Thanks guys,


Keanu
 
I can't download your file but you could possibly use the below.


If your dates start in A2:


=IF(AND(DAY(A2)=DAY(TODAY()),MONTH(A2)=MONTH(TODAY())),"yes","no")


This will put a yes or no in a helper column, you can then filter for all the ones marked yes and those are all the ones who have birthdays today.
 
Hi Keanu ,


If we assume that the specific date of 2nd July is in cell E1 , then , put in the following formula , as an array formula , using CTRL SHIFT ENTER :


=INDEX($B$2:$B$27,SMALL(IF(TEXT($C$2:$C$27,"dd-mm")=TEXT($E$1,"dd-mm"),ROW($C$2:$C$27)-MIN(ROW($C$2:$C$27))+1),ROW(A1)))


Copy this down , to get a list of all guests whose birthdate is the same as the date in E1.


Narayan
 
Keanur


Welcome back to the Chandoo.org forums


I have searched through the User DB and don't see any previous Keanur or use of your email address by previous users


There is a user Keanu email: ex-----55@hotmail.com, who has made 3 posts between 9 and 6 months ago.


The Ninja's can reset your password if you require, we just need your User Name or Email address. If you are Keanu and want to resume let us know
 
Hi Dave,


Thanks for your quick reply. i tried to use your magic formula, some cell indicating no and some return to value. not sure, why u can't see the link as i have tried before posted into this forum. perhaps any suggestions.


Thanks and regards,


Keanur
 
Hi Hui,


Thanks for the hand, infact i can't access my ex-----55@hotmail.com. i tried to reset the password but still could not make it. but yes, that is the first email i used to join chandoo. Yes i have some posts before and it is related to matching data by lookup different sheet, i think narayan991 and sirjb7 ever responded to my posts. can you send it to another email that i have? anyway, congratulation on your achievement today :)
 
Hi Narayank991,


i tried on my excel sheet, but still not come up with the proper result. column E2 return to value (1) while E3 and soon return with #NUM. can you elaborate a little bit, i'm not as fast you. can you send me a link where as i can see how i can implement this :(


thanks and regards,


keanur
 
Hi Keanur, can you make sure all your dates are in a date format as the #Value error would imply some are not


Thanks
 
@keanur

Hi!

Sometime ago I received an email from the address of the nick with lost password, so I have just sent you another mail to both adrresses with a reset password. Please change it after entering or advise if any issue.

Regards!


EDITED


PS: If successful I'd suggest to keep on using the original one.
 
Good day keanur


I know I am being a bit simplistic here but why not create a table and filter on dates


The link is the full web address


https://skydrive.live.com/redir?resid=B74F44A51EEC822B!183&authkey=!AMpq--ynkIB8nLw


select all of above and paste into browser perhaps OP could use Dropbox to simplify things.
 
Dear Dave,


it is simple and great result, my case is resolved. Thank you man


Dear Narayank991


cool formula, i have tried the formula. thanks


Dear Bobhc,


appreciate your link and advice


Regards,


keanur
 
You must log in or register to reply here.
Back
Top