+ Reply to Thread
Results 1 to 13 of 13

Create a list of birthdays in the next xx days

  1. #1
    Registered User
    Join Date
    08-22-2018
    Location
    NZ
    MS-Off Ver
    2016
    Posts
    10

    Create a list of birthdays in the next xx days

    Hi, I'm trying to create a table to display birthdays in the next xxx days, I guess a pivot table is the easiest way to do this.

    I have a list of data:

    Please Login or Register  to view this content.
    I now need to create a list of the birthdays that are coming up in the next xxx (say 28) days. If needbe I have worked out a forumula to calculate Next Birthday, days to go and age on next birthday as follow:
    Next B-Day:
    Please Login or Register  to view this content.
    Days to go:
    Please Login or Register  to view this content.
    Next Age: =INT(($C$1-C3)/365)+1

    Whats the easiest way to achieve this? All I want is something that will go through my list of data and pick out the people who have birthdays in the next xxx days, after that I intend to some how add a hyperlink that will email them.


    Dummy spreadsheet attached.
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,404

    Re: Create a list of birthdays in the next xx days

    Why have you posted in the charting and pivots area? If this is a mistake, I can move your thread.

    The E-mail question will require VBA and a new thread, once this one is solved.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    08-22-2018
    Location
    NZ
    MS-Off Ver
    2016
    Posts
    10

    Re: Create a list of birthdays in the next xx days

    Because I thought a pivot table is the best way to achieve this

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,404

    Re: Create a list of birthdays in the next xx days

    Your attachment is insufficient. In order to help, we need to see some source data as well as the lists you wish to produce. This should be dummy data manually mocked up. Thanks.

  5. #5
    Registered User
    Join Date
    08-22-2018
    Location
    NZ
    MS-Off Ver
    2016
    Posts
    10

    Re: Create a list of birthdays in the next xx days

    try that. I have no idea how to acheive this. I cant just dummy something up when I dont know how to do it
    Attached Files Attached Files

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,404

    Re: Create a list of birthdays in the next xx days

    Sorry - I did not make myself clear.

    By 'dummy up', what we mean is show the before and after you want, but do it MANUALLY. Then we can offer solutions. I am not asking you to do something you can't do.

    So, in your case, show us a sample of data as it is in your database, and on another tab show us what you want to achieve - no formulae, just simple text.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,404

    Re: Create a list of birthdays in the next xx days

    OK - so in the first cell of your birthday table, try this:

    =IFERROR(INDEX($A$1:$A$505,AGGREGATE(15,6,ROW($2:$500)/($G$2:$G$500=TRUE),ROW(1:1))),"")

    Then just use simple VLOOKUPs or INDEX MATCH formulae to get the other data required in the table:

    =VLOOKUP([@Name],Table2[[Name]:[Next B day]],2,0)

    =VLOOKUP([@Name],Table2[[Name]:[Next B day]],4,0)

  8. #8
    Registered User
    Join Date
    08-22-2018
    Location
    NZ
    MS-Off Ver
    2016
    Posts
    10

    Re: Create a list of birthdays in the next xx days

    thanks, I added that forumula, so thats checking my data and looking for the TRUE value next to within_28d?
    How do I get some sort of loop going so it will return as many results as match my query?

  9. #9
    Registered User
    Join Date
    08-22-2018
    Location
    NZ
    MS-Off Ver
    2016
    Posts
    10

    Re: Create a list of birthdays in the next xx days

    I also dont know how to use VLOOKUP or INDEXMATCH.. :/ im a bit hopeless and I've been banging my head against a wall for ages trying to get this to work

  10. #10
    Registered User
    Join Date
    08-22-2018
    Location
    NZ
    MS-Off Ver
    2016
    Posts
    10

    Re: Create a list of birthdays in the next xx days

    So (See attached) Ive managed to get the list of the names, it seems like it would be limited to a certain amount of names though and theres no way to filter the results so they display in order of next birthday due. Is there a way of doing that?
    Attached Files Attached Files

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,404

    Re: Create a list of birthdays in the next xx days

    It should bring through all names that match your query on the day you are looking at the file (i.e. have TRUE next to them).

    I am going to work now - hopefully someone else can take this up. If not, I'll have another look this evening.

  12. #12
    Registered User
    Join Date
    08-22-2018
    Location
    NZ
    MS-Off Ver
    2016
    Posts
    10

    Re: Create a list of birthdays in the next xx days

    thanks for your help, you've already been amazing

  13. #13
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Create a list of birthdays in the next xx days

    It looks as if you were able to apply the first formula AliGW gave you, so I just applied the others.
    I did slightly modify the first formula to match the structured reference form of the others.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 08-11-2017, 05:24 PM
  2. Trying to create a formula to count days based on 8 hour days
    By meadnl89 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-03-2016, 06:41 PM
  3. Calculating Birthdays in the last 30 days
    By bullodg in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-18-2015, 02:25 PM
  4. [SOLVED] Create a list from information that is 300 days past a nominated date
    By Gatheringinfo in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-07-2013, 03:15 AM
  5. Create List of Working Days
    By anandvh in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-24-2012, 06:06 PM
  6. create a worksheet that will list the days of the month
    By AlanWade in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 06-19-2008, 05:54 PM
  7. how can i put birthdays into the calandar from an list?
    By martha binford wells in forum Excel General
    Replies: 1
    Last Post: 10-18-2005, 04:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1