Automatic Birthday Reminder Sheet in Microsoft Excel

This article covers the guideline on how to make automatic birthday reminder sheet in Microsoft Excel.

Introduction and Importance

An automatic birthday reminder sheet in Microsoft Excel is a valuable tool for HR managers and organizations as it helps foster a positive and inclusive workplace culture. By ensuring that employee birthdays are remembered and acknowledged on time, it promotes employee engagement, strengthens team relationships, and boosts morale. This simple yet thoughtful system shows employees that they are valued, which can enhance job satisfaction and loyalty. Additionally, automating the process saves HR time and effort, reducing the risk of oversight and allowing them to focus on more strategic tasks.

Guideline on How to Create

Follow these steps to create automatic birthday reminder system in Excel.

1. Open an Excel workbook. Then save the workbook as Macro Enabled file type. (File/Save As/Choose macro enabled file type from the list/Save)
2. As shown in above image, In the sheet 1, Write Column Headers of Employee Name, Date of Birth, Upcoming Birthdays, Employee Name and Message in A1, B1, D1, E1 and F1 cell.
3. Rename the sheet 1 as “Date of Birth List”.
4. In A and B column, list out all the employee names and their date of birth. While putting the date of births, use proper date formatting that Excel understands as valid date.
5. Click on Developer Tab, then visual basic.
6. In the project explorer section of VBA window, double click on “This Workbook” Option.
7. In the code window, choose “Workbook” option from the dropdown at left side. This will create a workbook open sub procedure.
8. Between the Sub and End Sub, write the VBA code required for automatic birthday reminder. The required code you can copy from the below. Just copy and paste the code.
9. Save the workbook. Close it and Re Open.

The birthday reminder sheet is ready. If someone’s birthday is on today, it will pop up a birthday wish message box. If not, it will give you the list of upcoming birthdays considering the 15 days bracket.

Required VBA Code

Copy and paste the VBA code from below.

Dim ws As Worksheet
    Dim lastRow As Long, i As Long, outputRow As Long
    Dim empName As String
    Dim dob As Date, birthdayThisYear As Date
    Dim today As Date

    Set ws = ThisWorkbook.Sheets("Date Of Birth List")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    outputRow = 2
    today = Date

    ' Clear previous reminders
    ws.Range("D2:F100").ClearContents

    For i = 2 To lastRow
        empName = ws.Cells(i, 1).Value
        dob = ws.Cells(i, 2).Value
        birthdayThisYear = DateSerial(Year(today), Month(dob), Day(dob))

        If birthdayThisYear >= today And birthdayThisYear <= today + 15 Then
            ws.Cells(outputRow, 4).Value = Format(birthdayThisYear, "dd-mmm-yyyy")
            ws.Cells(outputRow, 5).Value = empName

            If birthdayThisYear = today Then
                ws.Cells(outputRow, 6).Value = "Happy Birthday to You, " & empName
                MsgBox "Happy Birthday to You, " & empName, vbInformation, "Birthday Alert"
            End If

            outputRow = outputRow + 1
        End If
    Next i

Things to Consider

Take care of below points while doing this.

Sheet Name

If your sheet name is something different than mentioned above, update the exact sheet name in the code.

Set ws = ThisWorkbook.Sheets("Date Of Birth List")

Replace Date of Birth List by your actual sheet name.

Time Bracket for Upcoming Birthdays

If you want to set different time bracket to identify upcoming birthdays (i.e 7 days) change the day number in below line of code.

If birthdayThisYear >= today And birthdayThisYear <= today + 15 Then

Replace 15 with your desired days number for identifying upcoming birthdays.

Video Tutorial

For step-by-step video tutorial on creating birthday reminder sheet in Excel, watch our YouTube video tutorial from the below.

Click Here to Subscribe Our YouTube Channel