I have posted this question before in google groups forum, and got half-solution. Pls have a look at this link to get an idea.

http://yogi--anand-consulting.blogsp...rth-dates.html

The solution was almost achieved, but the function posted by Mr. Yogi (the guy provided half solution) has some errors and now Mr. Yogi is unreachable.

Let me explain it once again.

Things i wish to achieve :I have a list of members and their birthdates, i am willing to enter this data manually. I am trying to create a sheet which can wish "happy birthday" to members having birthday today (current date) and a list of members having birthday in next 7 days (from current date). I can publish this sheet (particular cell range) on my webpage for the very purpose.

Have a look at sample sheet at below link, Feel free to edit but first make a copy of it so others can also have their say.

https://docs.google.com/spreadsheets...it?usp=sharing

Incase you are not getting it, Let me explain everything once again

A1&B1has headings called NAMES & BIRTHDATES respectively.

A2toA2000has names (i.e user1, user2, user3 and so on)

&B2toB2000has birthdates in DD/MM/YYYY format (i.e 13-12-1980, 25-05-1990, 31-2-1984) years can be anywhere between 1950 to year 2000.

(i have this names and birthdates data with me, i will put it manually one by one or i can publish a spreadsheet form online for my users). So No formulas here in column A & B.

D2- this cell has no formula, but we can input date here (this is just for checking sake, so whenever my sheet gets its final formula, i can check it with any date i wish)

E2- this cell has formula, it checks if D2 has some date, if yes then it pulls d2 data, if d2 is empty, it shows current date by this formula

Formula:Please Login or Register to view this content.

D6- this cell has formula, it checks the whole list of dates in B2 to B2000 or can say whole column B, if any date matches with E2 (which is current date, or the date written by me in D2) then it pulls out name(s) of that user(s) and shows it with commas. For example if value in E2 is 16-11-2014 then D6 formula check this date with full column B, if it finds any match, it pulls the name in same row of column A and shows it here. And if no matches found, it shows "No Members Birthday Today". Formula for D6 is

Formula:Please Login or Register to view this content.

E9- This cell has no formula, its there just to put some figure (number of days) which can be used in formula of D11. For example if we enter number "7" in cell E9, then the formula in D11 will sort list of users having birthdays in upcoming 7 days (from current date or from date entered in D2). For example if D2 is empty, E2 will show current date, say its 16-11-2014, If we put number 7 in E9 then D11 formula searches for users/birth dates having birthday in upcoming 7 days (17-11-2014 to 23-11-2014) and display list of users with dates in D11.

Now here comes the MONSTER

D11- This cell has formula, infact most important formula for this sheet (which is currently not perfect).

Say if E2 has a date of 16-11-2014 & E9 has number 7. Then D11 formula should check/search next 7 days dates in column B and if it finds any match, it should pull username from same row (from column A) and should display something like this

17/11 - user3, user888, user 75. (these user's birthday is on 17/11, which is next day from current date (upcoming day))

18/11 - user22, user975, user 666, user 1091 (these user's have birthday on 18/11, which is day after tomorrow from current date.)

20/11 - user99 (this user birthday on 20/11)

Above list can go till maximum 7 rows or less, because i have given range of 7 days in cell E9.

Now, Mr.Yogi helped me in D11 formula, but formula is not complete, it has some errors, Formula for D11 is

Formula:Please Login or Register to view this content.

Above formula has some errors, it doesnt works if we enter year ending dates like 31-12-2014 in D2 and range of 7 days in E9. Pls fix this so it can show upcoming birthdays of users having birthday in upcoming 7 days (January)

Another error I found was, if we enter dates like 29-02-2014, D11 says#N/Awhy so ! All other dates are working fine, then why animosity with february last day.

2 more things i wish to change,

1.D11 should show date first and names afterwards (instead of names first and dates afterwards)

Like This

15-04 - user1, user2

21-04 - user3, user4

27-04 - user11, user6, user88, user509

I think this will be easy for you, just changing positions (though i dont know, how to do it )

2.Dates in cell D11 are not in order, Pls fix this. it should be like this

15-04 - user1, user2

21-04 - user3, user4

27-04 - user11, user6, user88, user509

and not like this

21-04 - user3, user4

15-04 - user1, user2

27-04 - user11, user6, user88, user509

Query & Explanation over.

Few suggestions(ideas) coming in my mind:

#2 would be easy i think, I dont know how to do it, But by anyway if we can sort column B by dates (ascending) (or by pulling and sorting it to column C). As far as i can understand, i think YEAR has no role in any formula, formula works only on mm (month) and dd (date), so why not to sort only dd/mm in column C and then pull it to D11 formula. Well this is just a guess

Thanks for all your time and efforts.

Waiting ...

## Bookmarks