Not able to solve this Spreadsheet Function, Pls Help

[intradayteam]

Using Google Spreadsheet I want to add a Upcoming Birthday Event Sheet to my website ( I heard that i am allowed to display (embedd) particular cells on any webpage)


Here are the conditions i wish to solve.


Suppose A1 to A1000 have a list of names & B1 to B1000 have dates (birth dates in dd/mm/yyyy format)


Names & Birthdays from current date to the upcoming 15 days should be sorted in C1 to C15.


If anyones or more than one's birthday is on current date its name should be displayed in D1 to Dn (n is number of birthdays on that particular day, it can be more than one or two). It doesnt matters if all the names be displayed in D1 with commas.

Here i am guessing that C1:C15 & D1 to Dn will update automatically as they will be given formulas according to dates and date changes everyday.


Actually i want to add iframe or widget to my website which can wish Happy Birthday to the users having birthday on current date and display Birthdays of users for upcoming 15 days. (i have the list of names and birthdays with me).


Here is the sample just for an idea....


Happy Birthday "User1" "User2" (this should be in D1)

Upcoming Birthdays (its just a heading of column C)

"Name" - "Date"

"Name" - "Date"
"Name" - "Date"
"Name" - "Date"

"Name" - "Date"


these "Name" - "Date" should be automatically sorted in order to upcoming 15 days from current date. For example if today's date is 01/11/2014 (dd/mm/yyyy) then D1 should display names of user(s) having birthday on current date and C1 to C15 should display names of users having birthday in next (upcoming) 15 days.


I tried my best to explain my query, Anyone can help with the formula (function) !!!


Pls do mention the cell address where i can paste the particular fuction. i.e put this function in D1.


Thanks a lot.

 

[mole999]

have you got the authority of those you will be showing publicly. Its the sort of thing hackers love to collate

 

[Tyron]

Hey intradayteam,

All that really sounds like is happening is that you have a list of birthdays and that you want to sort them in date order from today's date through to the next 15 days. I am not familiar with google spreadsheet, but it sounds like something that I have seen posted before.

Anyone have any ideas that has experience using the google spreadsheet?

later

Ty

 

[intradayteam]

@ Mole999, Yes bro i have that authority.

@ Tyron, You got the point. I know its easily possible in Excel, but i am not familiar with google spreadsheet. Once the sheet gets automated, I can publish particular cells over my website.

 

[Tyron]

Hey intradayteam,

I found this web page that may be of some help, but otherwise I will have to fiddle around a bit.

https://support.google.com/docs/table/25273?hl=en

later

Ty

 

[intradayteam]

@ Tyron, I have seen that page before, Not able to figure it out.

 

[Tyron]

Hey intradayteam,

Okay. Do you need to have a google account in order to create, modify, use the google spreadsheets?

In the meanwhile I will check out the glossary myself. My guess is it is possible, but you will need to use helper cells. How many birthdays are you keeping track of?

later

Ty

 

[Tyron]

Hey intradayteam,

Okay - worked out the bulk.

You will need the following:

1. cell which holds the value of TODAY()
2. a table or range of values that have three columns: Name, Birthdate, This Year.
3. column of cells which will hold the 15 days of dates you want to be checking.

This is how I envision it. I have made sure that there is a comparable equivelant to excel in the google formula list.

	A	B	C	D	E	F	G	H	I
1		Today's Date:	05/22/2014
2		Birth	This		05/22/2014	None
3	Name	Date	Year		05/23/2014	None
4	Mary	05/26/2000	05/26/2014		05/24/2014	None
5	John	01/24/1988	01/24/2014		05/25/2014	None
6	Ricky	05/28/1962	06/12/1962		05/26/2014	Mary
7					05/27/2014	None
8					05/28/2014	Ricky
9					05/29/2014	None

<tbody>
</tbody>

You will have to input the formulas, but otherwise you should be good. As I have never worked with Google sheets I am not sure if you use the "=" to start off formulas.

Using my example you have to input the values in A4:B??. In column C you will use:

=DATE(2014,MONTH(B1),DAY(B1))

Cell C1 will just be:

=TODAY()

Cell E2 will be:

=(C1)

You may not need the "()"

Cell E3 will be:

=(E2+1)

You will just copy it down so the next will be:

=(E3+1)

and keep going until you have the other 14 days listed.

In F2 I have copied down the formulas as such.

IF(ISNA(INDEX($A$4:$B$9, MATCH(E2,$C$4:$C$9,0),1)),"NONE",INDEX($A$4:$B$9, MATCH(E2,$C$4:$C$9,0),1))

Now as the day changes every name should move towards the top (today's date). I tested this by manually entering the date rather than using the today() function. So all names will raise to the first date and then disappear.

Also, color is not included in the formulas. I just put that in to show you the key items.

Once you get this working you will have to get some more help as I am not experienced in the listing multiple people with same BDAY when it comes to google sheet array formulas.

Half the battle is over though. lol.

let me know once you have it working.

later

Ty

 

[intradayteam]

Sorry for the late reply, was busy with some personal work. My apologies to all the members and specially @ Tyron.

I posted this question to many forums but i got very few replies. Thanks to all the geek's community, i think i am close to solution. Here are the updates.

Here is the link to my google spreadsheet https://docs.google.com/spreadsheets/d/1_qB5Btj8ldQGEEN-7y6ARZqJPS3EN2xIxSybz2nWNX8/edit?usp=sharing (sheet has all the formulas in it, i have shared this sheet with EDIT allowed in google product forum too)

A guy named Yogi helped me making first step towards solution, Here is the link to his blog to get an idea Cloud Computing -- Google Docs Way: yogi_From A Table Of Names And Birth Dates Pull A List Of Names As Of A Reference Date And There About

Now that guy is unreachable & i am stuck in between. There are few errors in given function.

Before finalizing my sheet i realized few errors. Say if i enter dates like 29-02-2014 in D2, D6 shows nothing and D11 says N/A. Why so! Pls fix this. Here are the few changes i would like to point out.
(1) In cell D11 sorting users according to 15 days is really not a good idea, Can we sort it according to number of users like 15. I mean D11 should have formula having list of 15 users having upcoming birthdays. (if its not possible or difficult to achieve then let it be as it is)
(2) If D6 have no data to show than instead of showing total blank cell, it should show "No Member's Birthday Today".
(3) Formula doesnt work if we enter year ending dates like 31-12-2014, pls fix this so it can show upcoming birthdays of january, february whatever comes in 15 user/days range.

(4) Dates in D11 to D15 are not in order, it should be like



15-04 - user1, user2

21-04 - user3, user4

27-04 - user11, user6

and NOT like
21-04 - user3, user4
15-04 - user1, user2
27-04 - user11, user6


Thanks

 

[intradayteam]

Anybody listening ?