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

Makhaira · 26 Oct 2010 at 20:55

Just add 30 to today's date when you do the comparison

.

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.

Makhaira · 26 Oct 2010 at 20:59

Something like this will work (not in Excel so you might need to correct my syntax a bit).

=IF(AND(K10=>TODAY(),K10=<(TODAY()+30)),"TRUE",FALSE)

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

Hagar · 1 Nov 2010 at 16:24

I think that you will need to use VBA or VB.net to perform this task.

sample

Sub mailto_Selection()
'David McRitchie, 2005-08-01 modified for selection,
' http://www.mvps.org/dmcritchie/excel/email.htm
Dim Email As String, Subj As String, cell As Range
Dim response As Variant
Dim msg As String, url As String
Email = "" 'create list below
Subj = "Family Newsletter"
msg = "Dear Family,"
'-- Create the URL

For Each cell In Selection
Email = Email & cell.Text & "; "
Next cell

url = "mailto:" & Email & "?subject=" & Subj & "&body=" _
& Replace(msg, Chr(10), "/" & vbCrLf & "\")
MsgBox url
url = Left(url, 2025) 'was successful with 2025 , not with 2045
'-- Execute the URL (start the email client)
ShellExecute 0&, vbNullString, url, vbNullString, vbNullString, vbNormalFocus
End Sub

You will need to define range, which will be your liting from above

andy.

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.