A birthday reminder on your phone pops up the morning of. That's fine for a text. It's too late for a gift that has to ship, or for noticing that three people you love all have birthdays in the same week of March.
Here's a birthday calendar template you build once in Google Sheets: a list that works out the 2027 date, age and weekday for every birthday, and a one-page year view where each one is highlighted so you can see the whole year of birthdays at once.
TL;DR
- You get a birthday list that calculates 2027 dates and ages, plus a one-page year with each birthday highlighted.
- 2027 has no February 29, so leap day birthdays need a decision before you add them.
- The common mistake is typing birthdays only into a phone, where you can't see the busy months.
Who this is for
- Office Manager: Every coworker's birthday for the year sits on one page, so cake doesn't get forgotten.
- HR Coordinator: You can plan a monthly birthday celebration by seeing which months are crowded.
- Parent: Your kids' friends' birthdays show up early enough to buy a gift before the party.
- Teacher: Student birthdays, including summer ones, can be celebrated on a school day you choose.
- Executive Assistant: Your manager's key birthdays show up with enough notice to send a card or gift.
Build the birthday list
Start a new tab called BIRTHDAYS with four columns:
- A: Name
- B: Birth date (with the year if you know it, like 6/14/1990)
- C: Birthday in 2027:
=DATE(2027,MONTH(B2),DAY(B2)) - D: Turns:
=2027-YEAR(B2)
Add a fifth column for the weekday with =TEXT(C2,"dddd"). Knowing a birthday lands on a Saturday changes whether you plan a dinner or just a text.
Fill the formulas down and format column C as a date. If you don't know someone's birth year, enter any year and ignore column D for that row.
To sort the whole list by date, put this in a spare spot, like G2:
=SORT(A2:C,C2:C,TRUE)
Empty rows go to the bottom, so you can keep adding people to the main list.
What to do with February 29 birthdays
2027 isn't a leap year. The next February 29 is in 2028. For a leap day birthday, =DATE(2027,2,29) rolls over to March 1, 2027 in Google Sheets, because there's no 29th that year.
Some people celebrate on February 28 and some on March 1. Ask the person, then type that date into column C for their row instead of the formula.
Put every birthday on one page
A list is good for looking up one person. To see the year, I use the free Big Year calendar. The BIG tab shows all 12 months of 2027 on one page in Google Sheets.
- Copy column C from your BIRTHDAYS tab.
- In Big Year, open the SETTINGS tab and paste the dates into the Set Your Holidays column (the left one) with Edit, Paste special, Values only.
- Go to BIG. Every birthday is now highlighted on the year view.
- Type the person's name into each highlighted day box, so you can read whose birthday it is.
Now you can see things a list hides. Maybe March has six birthdays in two weeks and you'll want to order gifts early. Maybe nobody you know was born in August.
For a paper copy, the DOWNLOAD tab makes a one-page PDF of the year with your notes and highlighted dates. Letter or A4 works on a fridge or desk; Tabloid or A3 works on a wall.
A birthday calendar you reuse every year
Birthdays don't change, but calendars do. Next year, change 2027 to 2028 in columns C and D and copy the new dates in again. If you'd rather have one sheet that never goes out of date, the UNDATED tab in Big Year Premium ($10 for 10 templates) has 12 rows of 31 blank day cards with no dates on them. Write the month names down the side and each name on its day, and print it again every year.
Before you close the sheet, set a weekly reminder to look at the next two weeks on BIG, so gifts and cards go out on time.
Common pitfalls
- Typing birth dates as text: If the date sits left-aligned, Sheets is reading it as text; retype it in your locale's date format so the formulas work.
- Ignoring leap day birthdays: Decide between February 28 and March 1 for 2027 and type it in by hand.
- Highlights with no names: A highlighted day doesn't say whose birthday it is, so add names in the day boxes on BIG.
- Only keeping the list on your phone: A phone shows one reminder at a time, so keep the year view where you'll see it.
FAQ
How do I make a birthday calendar in Google Sheets?
List names and birth dates, calculate each birthday in 2027 with =DATE(2027,MONTH(B2),DAY(B2)), then highlight those dates on a one-page year view.
How do I calculate how old someone turns in 2027?
Use =2027-YEAR(B2), where B2 is their birth date.
Is 2027 a leap year?
No. 2027 has 365 days. The next leap day is February 29, 2028.
Can I print a birthday calendar?
Yes. Big Year's DOWNLOAD tab makes a one-page PDF in Letter, A4, Tabloid or A3, with your birthdays highlighted.
What is a perpetual birthday calendar?
One without dates or weekdays, so it works every year. Big Year Premium's UNDATED tab can be used that way.
Planning next year? Get the Big Year, a free 2027 calendar in Google Sheets.
