Anniversary/Birthday Dashboard

[Jayrey]

Hi!

I'd like to create a dashboard for my operation to display birthdays and anniversaries.

I have a master sheet of employee names, their birthdays and anniversaries (sheet 2). And I'd like to create a dashboard page to display only what I need(sheet 1)

I'm trying to say to excel,: If a birthday from i.e. column B is equal to todays date, then display the corresponding name from i.e. column A on sheet 2. Same for anniversaries.

I'd also like a list that lists all upcoming birthdays and anniversaries if equal to the current month.

Bonus awesomeness if there's a way to populate years of service for anniversaries!

My coworkers say it can't be done. Say it ain't so!

TYIA

 

[6StringJazzer]

Your co-workers are sadly mistaken.

I created a bunch of fake data from formulas because I was not going to type yours in by hand (suggestion for future: paste data into your post rather than a screenshot).

The formulas shown at the bottom in green are spilling array formulas. You just enter them once in row 3 and they automatically fill down.

I assumed that the birthday column is real dates, just not showing the year. If that's not the case then this gets a lot more complicated.

$scratch.xlsm
	A	B	C	D	E	F	G	H
1	Todays Birthdays		Today's Anniversaries	Years of Service		This Month's Birthdays		This Month's Anniversaries
2	Wednesday, August 21, 2024		Wednesday, August 21, 2024			August 2024		August 2024
3	Name 4		Name 7	4		Name 4		Name 2
4	Name 102		Name 41	4		Name 9		Name 7
5	Name 118					Name 14		Name 20
6						Name 18		Name 26
7						Name 29		Name 30
8						Name 64		Name 33
9						Name 66		Name 41
10						Name 83		Name 43
11						Name 91		Name 62
12						Name 92		Name 83
13						Name 101		Name 86
14						Name 102		Name 87
15						Name 117		Name 92
16						Name 118		Name 106
17						Name 123		Name 111
18						Name 138		Name 117
19						Name 170		Name 138
20						Name 174		Name 139
21						Name 183		Name 144
22						Name 189		Name 154
23						Name 209		Name 166
24								Name 171
25								Name 175
26								Name 200
27								Name 205
28								Name 206
Anniversary Dashboard

Cell Formulas
Range	Formula
C2,H2,F2,A2	C2	=TODAY()
A3:A5,C3:C4	A3	=IFERROR(FILTER('Employee Data'!$A2:$A999,DATE(YEAR(TODAY()),MONTH('Employee Data'!B2:B999),DAY('Employee Data'!B2:B999))=TODAY()),"None today.")
D3:D4	D3	=IFERROR(DATEDIF(FILTER('Employee Data'!$D2:$D999,DATE(YEAR(TODAY()),MONTH('Employee Data'!D2:D999),DAY('Employee Data'!D2:D999))=TODAY()),TODAY(),"Y"),"None today.")
F3:F23,H3:H28	F3	=IFERROR(FILTER('Employee Data'!$A2:$A999,MONTH('Employee Data'!B2:B999)=MONTH(TODAY())),"None today.")
Dynamic array formulas.