Upcoming Birthday Report

oberning

I would like to include a Report Widget that shows upcoming birthdays, but it doesn't seem possible to only pull data according to month and date (whenever I use the date column, it automatically adds the year -- 2018, in this case). Is there anyway that I can pull data for upcoming birthdays w/o relying on the year they were born?

Thank you!

Chris McKay

Hi,

You won't be able to do this without adding some additional columns in the data sheet containing the list of birthdays (with year).

1. Add in 2 columns (Day & Month) using the default Text/Number format
2. Now add in a Birthday column and set this to a Date format
3. In the Day column, enter the following formula:
   
   =VALUE(DAY(DOB1))
   
   where DOB is the column you store the person's DOB
4. In the Month column, enter the following formula:
   
   =VALUE(MONTH(DOB1))
   
   again where DOB is the column storing DOB for each person
5. In the Birthday column, enter the following formula:
   
   =DATE(YEAR(TODAY()), Month1, Day1)

This will populate Birthday with the person's day and month of birth and the current year. This is how you will trigger the report to show only rows that are within X days of the Birthday date.

You could get clever and add in more columns to translate the Month value into a text string e.g. IF(Month1 = 1, "January", IF(Month2 = 2, February etc......

Kind regards,

Chris McKay