Excel formula help

reefoid · 26 Oct 2010 at 20:52

Can anyone help with an Excel formula please? I have a sheet of client data (names, addresses, email, etc). It has a date of birth column and I want to create a new sheet that will display only people with birthdays in the next 30 days.

I don't have much Excel knowledge and I seem to be going round in circles with the help system. Would this be the correct process:

1. Add an extra column to the existing sheet that would contain a flag if the birthday is in the next 30 days.
2. Use an advanced filter to add those records to another sheet.

Is that right?

For step 1, I can set a flag if the dob matches today's date using:

Code:

=IF(MONTH(K10)<>MONTH(TODAY())," ",IF(DAY(K10)<>DAY(TODAY())," ","Flag"))

But how do I expand this so it flags if it matches any date in the next 30 days?

Thanks

reefoid · 26 Oct 2010 at 20:57

But won't that only show records that match the date in 30 days time? I need anything within the next 30 days.

reefoid · 1 Nov 2010 at 15:58

OK, almost got this working now. I created a new column that shows their next birthday then used the following code to identify upcoming birthdays:

=IF(AND(N18>=TODAY(),N18<=TODAY()+30),"BIRTHDAY"," ") (thanks Makhaira)

Can anyone help with the next step. I need to create a link for each record that when clicked on will automatically open a mail in Outlook and populate with their email address, a subject line, and then their name and birthday within some predefined body text.

I've googled but it all seems very technical. Does anyone know of a decent guide/tutorial for doing this?

Thanks

reefoid · 1 Nov 2010 at 16:30

Yes, seems VBA is the way to go. Found some code that looks like it will do the trick. Just need to customise for my needs.

Thanks.