Formula to keep track of when to send birthday cards

[MelanieW]

... I know, weird question!

I have a large office where the PM's send out bday cards regularly. I need a formula taking the receivers birthday (example: 31/12/2015, day/month/year) and finding out when I need to send the next card for a 2016 excel workbook. I need to factor in 5 business days for it to arrive on time in the mail.

I've searched this forum in the past and its always been extremely helpful!! Signed myself up today!

Thanks and Happy Holiday Season!

[shg]

Welcome to the board.

If you have a file with each employees DoB, you can add a column that calculates their next birthday (on or after today):

=EDATE(DoB, 12*(DATEDIF(DoB+1, TODAY(), "y") + 1))

Sort by that and you'll see whose birthdays are coming up.

[Tony Valko]

With the BD in cell A1...

=WORKDAY(A1,-5,Holidays)

Format as Date

Where Holidays is a list of holiday dates to be accounted for in the calculation.

[MelanieW]

shg
Re: Formula to keep track of when to send birthday cards
Welcome to the board.
If you have a file with each employees DoB, you can add a column that calculates their next birthday (on or after today):
=EDATE(DoB, 12*(DATEDIF(DoB+1, TODAY(), "y") + 1))
Sort by that and you'll see whose birthdays are coming up.

Thanks shg!

I've tried this and formatted the cell to date however it did not work. Where do I put the cell reference in this formula?

[MelanieW]

I've tried this as well and it did not work. The cell referenced the birthday in date format. Any suggestions?

Mel

[Tony Valko]

What version of Excel are you using?

Both the EDATE function and the WORKDAY function need to have the Analysis ToolPak add-in be installed in Excel versions 2003 and earlier.

[shg]

Add the formula:

Row\Col	A	B	C	D
1	Name	DoB	next B'day
2	Alan	25 Aug 1993	25 Aug 2016	B2: =EDATE(B2, 12*(DATEDIF(B2+1, TODAY(), "y") + 1))
3	Barb	08 Jan 1991	08 Jan 2016
4	Cain	08 Jan 1970	08 Jan 2016
5	Dana	30 Sep 1977	30 Sep 2016
6	Eric	14 Feb 1960	14 Feb 2016
7	Fran	30 Sep 1963	30 Sep 2016
8	Gary	09 Aug 1969	09 Aug 2016
9	Hana	26 Feb 1973	26 Feb 2016
10	Ivan	16 Jun 1970	16 Jun 2016
11	Jane	03 Jul 1995	03 Jul 2016
12	Kent	04 Nov 1979	04 Nov 2016
13	Leah	01 Nov 1964	01 Nov 2016
14	Mark	07 Aug 1982	07 Aug 2016
15	Nina	02 Dec 1971	02 Dec 2016
16	Otto	11 Jul 1962	11 Jul 2016
17	Peri	24 Mar 1965	24 Mar 2016
18	Quin	22 Sep 1988	22 Sep 2016
19	Rene	03 Jun 1968	03 Jun 2016
20	Seth	14 Sep 1967	14 Sep 2016
21	Tina	24 Dec 1978	24 Dec 2015
22	Ulis	21 Aug 1967	21 Aug 2016
23	Vera	17 Jan 1986	17 Jan 2016
24	Wade	15 Jun 1960	15 Jun 2016

Then sort by the Next B'Day column:

Row\Col	A	B	C	D
1	Name	DoB	next B'day
2	Tina	24 Dec 1978	24 Dec 2015	C2: =EDATE(B2, 12*(DATEDIF(B2+1, TODAY(), "y") + 1))
3	Barb	08 Jan 1991	08 Jan 2016
4	Cain	08 Jan 1970	08 Jan 2016
5	Vera	17 Jan 1986	17 Jan 2016
6	Eric	14 Feb 1960	14 Feb 2016
7	Hana	26 Feb 1973	26 Feb 2016
8	Peri	24 Mar 1965	24 Mar 2016
9	Rene	03 Jun 1968	03 Jun 2016
10	Wade	15 Jun 1960	15 Jun 2016
11	Ivan	16 Jun 1970	16 Jun 2016
12	Jane	03 Jul 1995	03 Jul 2016
13	Otto	11 Jul 1962	11 Jul 2016
14	Mark	07 Aug 1982	07 Aug 2016
15	Gary	09 Aug 1969	09 Aug 2016
16	Ulis	21 Aug 1967	21 Aug 2016
17	Alan	25 Aug 1993	25 Aug 2016
18	Seth	14 Sep 1967	14 Sep 2016
19	Quin	22 Sep 1988	22 Sep 2016
20	Dana	30 Sep 1977	30 Sep 2016
21	Fran	30 Sep 1963	30 Sep 2016
22	Leah	01 Nov 1964	01 Nov 2016
23	Kent	04 Nov 1979	04 Nov 2016
24	Nina	02 Dec 1971	02 Dec 2016

[MelanieW]

shg! Amazing, this worked! Can I trouble you for one more thing? I'd need it to calculate the next birthday (TODAY) minus 5 days. Can it be built into the formula or does this need to be done in a separate column? Preferred would be a single formula.

[shg]

Subtract 5 from the result.

[MelanieW]

I used this formula however it came back with a result 5 months prior to the birthday

=EDATE(K29, 12*(DATEDIF(K29+1, TODAY(), "y") + 1) - 5)

I've tried - 5, "d") however this was not valid.

[shg]

B2: =EDATE(B2, 12*(DATEDIF(B2+1, TODAY(), "y") + 1)) - 5

[MelanieW]

shg This has worked and you are amazing!!!! Thank you and Happy Holidays!!!

[shg]

You're welcome.