Similarly, the ranges\u00a0A5:G5,A7:G7,A9:G9,A11:G11,A13:G13,A15:G15 use the exact same formulas as above.<\/p>\n
In case you’re curious, the colors I used were the yellow color preset “light yellow 3” and the blue I used was a custom shade of blue with RGB values of 217, 229, 252.<\/p>\n
Well, I wrote this very simple query formula to query the birthdays. This formula came from A5 of the #Birthday #Calendar sheet.<\/p>\n
=<\/span>IFERROR<\/span>(<\/span>IF<\/span>(<\/span>ABS<\/span>(<\/span>A4<\/span>)<\/span>=<\/span>0<\/span>,<\/span>“”<\/span>,<\/span>JOIN<\/span>(<\/span>” “<\/span>,<\/span>QUERY<\/span>(<\/span>Database<\/span>,<\/span>“select Z where A is not null and Y = “<\/span>&<\/span>DATEVALUE<\/span>(<\/span>A4<\/span>&<\/span>” “<\/span>&<\/span>$H$7<\/span>)<\/span>)<\/span>)<\/span>)<\/span>,<\/span>IF<\/span>(<\/span>DAY<\/span>(<\/span>TODAYDATE<\/span>)<\/span>–<\/span>A4<\/span>=<\/span>0<\/span>,<\/span>IF<\/span>(<\/span>Database!$H$7<\/span>=<\/span>“Yes”<\/span>,<\/span>“There are no birthdays today”<\/span>,<\/span>“”<\/span>)<\/span>,<\/span>“”<\/span>)<\/span>)<\/span><\/p><\/blockquote>\nBasically, what this does is it querys the Database, which is another named range which contains the entire Database sheet. The query basically displays column Z, which is the formatted display where the birthday is equivalent to that day. If it can’t find anything, it will output an error, which is why I put an IFERROR to catch it and display, according to the settings, either “There are no birthdays today”, or leave the cell blank.<\/p>\n
Calculating Ages<\/h5>\n
To calculate the ages of the people to be displayed, I came up with the following formula (from Z2 of the Database sheet):<\/p>\n
=<\/span>IFERROR<\/span>(<\/span>IF<\/span>(<\/span>$H$5<\/span>=<\/span>“Yes”<\/span>,<\/span>IF<\/span>(<\/span>AND<\/span>(<\/span>NOT<\/span>(<\/span>ABS<\/span>(<\/span>B2<\/span>)<\/span>=<\/span>0<\/span>)<\/span>,<\/span>ABS<\/span>(<\/span>C2<\/span>)<\/span>=<\/span>0<\/span>)<\/span>,<\/span>IF<\/span>(<\/span>$H$12<\/span>=<\/span>“(??)”<\/span>,<\/span>A2<\/span>&<\/span>” (??)”<\/span>,<\/span>A2<\/span>)<\/span>,<\/span>A2<\/span>&<\/span>” (“<\/span>&<\/span>DATEDIF<\/span>(<\/span>CONCATENATE<\/span>(<\/span>TEXT<\/span>(<\/span>$B2<\/span>,<\/span>“mm\/dd”<\/span>)<\/span>,<\/span> “\/”<\/span>,<\/span> $C2<\/span>)<\/span>,<\/span> TODAYDATE<\/span>+<\/span>31<\/span>,<\/span> “Y”<\/span>)<\/span>&<\/span>“)”<\/span>)<\/span>,<\/span>A2<\/span>)<\/span>,<\/span>“”<\/span>)<\/span><\/p><\/blockquote>\nYeah, it’s kind of long, but basically what it does is it takes the difference between the two dates and formats it accordingly to your setting choice. If there’s no year, it will either display the name of the person with a “(??)” instead of an age, or it will display just their name, depending on the setting you chose. TODAYDATE is a named range which contains the formula =TODAY() for optimization purposes. I use the date difference (DATEDIF) between their birthday and today + 31 days so that it can calculate the age of the people at the end of the month correctly when it’s still the beginning of the month.<\/p>\n
The Now? Column<\/h5>\n
The Now? Column basically displays a crown whenever today is their birthday. It uses a very simple formula that I came up with (from D2 of the Database sheet):<\/p>\n
=<\/span>IFERROR<\/span>(<\/span>IF<\/span>(<\/span>DATEVALUE<\/span>(<\/span>B2<\/span>)<\/span>=<\/span>DATEVALUE<\/span>(<\/span>TEXT<\/span>(<\/span>TODAYDATE<\/span>,<\/span>“mm\/dd”<\/span>)<\/span>)<\/span>,<\/span>“\u265b”<\/span>,<\/span>“”<\/span>)<\/span>,<\/span>“”<\/span>)<\/span><\/p><\/blockquote>\nBasically, it checks if the Month+Day column (B) has the same date as today. If it does, then it will display a crown. If not, then it won’t.<\/p>\n
Settings<\/h5>\n
With the settings, creating the dropdown menus were pretty simple. If you don’t know how to do so, right click on the cell you want dropdown menus for, and select Data Validation. ![\"sirknightj](\"https:\/\/www.sirknightj.com\/blog\/wp-content\/uploads\/2019\/09\/sirknightj-data-validation.png\")
<\/a>That is how I created the dropdown menus by checkmarking the box that says Show dropdown list in cell and putting a list of items down. How to make the settings actually do something is by using a bunch of IF statements to see which of the options were chosen. For example, this is the formula I used to display the title of the birthday calendar (from A1 of the #Birthday #Calendar sheet):<\/p>\n=<\/span>IF<\/span>(<\/span>Database!H9<\/span>=<\/span>“Month Year Birthdays”<\/span>,<\/span>TEXT<\/span>(<\/span>TODAYDATE<\/span>,<\/span>“MMMM YYYY Birt\\h\\da\\y\\s”<\/span>)<\/span>,<\/span>IF<\/span>(<\/span>Database!H9<\/span>=<\/span>“Month Birthdays”<\/span>,<\/span>TEXT<\/span>(<\/span>TODAYDATE<\/span>,<\/span>“MMMM Birt\\h\\da\\y\\s”<\/span>)<\/span>,<\/span>IF<\/span>(<\/span>Database!H9<\/span>=<\/span>“Month Year”<\/span>,<\/span>TEXT<\/span>(<\/span>TODAYDATE<\/span>,<\/span>“MMMM YYYY”<\/span>)<\/span>,<\/span>TEXT<\/span>(<\/span>TODAYDATE<\/span>,<\/span>“MMMM Birt\\h\\da\\y\\s YYYY”<\/span>)<\/span>)<\/span>)<\/span>)<\/span><\/p><\/blockquote>\nIt’s really simple, but looks complicated because there were 4 different options to choose from. Hope this gives you some insight into how I created this amazing Birthday Calendar spreadsheet!<\/p>\n","protected":false},"excerpt":{"rendered":"
Hey everyone, I’d like to share with you this amazing birthday calendar I’ve created via Google Sheets! Here it is: Birthday Calendar Template<\/p>\n","protected":false},"author":2,"featured_media":55,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[3,4,5],"tags":[],"_links":{"self":[{"href":"https:\/\/spreadsheets.sirknightj.com\/index.php\/wp-json\/wp\/v2\/posts\/54"}],"collection":[{"href":"https:\/\/spreadsheets.sirknightj.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/spreadsheets.sirknightj.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/spreadsheets.sirknightj.com\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/spreadsheets.sirknightj.com\/index.php\/wp-json\/wp\/v2\/comments?post=54"}],"version-history":[{"count":0,"href":"https:\/\/spreadsheets.sirknightj.com\/index.php\/wp-json\/wp\/v2\/posts\/54\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/spreadsheets.sirknightj.com\/index.php\/wp-json\/wp\/v2\/media\/55"}],"wp:attachment":[{"href":"https:\/\/spreadsheets.sirknightj.com\/index.php\/wp-json\/wp\/v2\/media?parent=54"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/spreadsheets.sirknightj.com\/index.php\/wp-json\/wp\/v2\/categories?post=54"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/spreadsheets.sirknightj.com\/index.php\/wp-json\/wp\/v2\/tags?post=54"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"sirknightj](\"https:\/\/www.sirknightj.com\/blog\/wp-content\/uploads\/2019\/09\/sirknightj-calendar.png\")
<\/a>The calendar I made by using BenAtShaw’s video<\/a>, which I modified to fit these needs. It’s not exactly the same as his, but I used his to base this design off of. There are a lot of conditional formatting going on in this sheet.<\/p>\n![\"sirknightj](\"https:\/\/www.sirknightj.com\/blog\/wp-content\/uploads\/2019\/09\/sirknightj-conditional-formattings.png\")
<\/a> To make those same cells yellow to highlight the day today, I use the custom formula: =AND(DAY(TODAY())-A4=0,$H$6=”Yes”). The $H$6=”Yes” part is the settings asking whether you want today to be highlighted. If it’s on “No”, then it won’t be highlighted.<\/p>\n