Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Birthday Calendar - recurring date that automatically updates each year

Topic Labels: Dates & Timezones Formulas
Solved
Jump to Solution
3613 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Meagan_Caesar
6 - Interface Innovator
6 - Interface Innovator

Hi!

So I'm trying to set up a table for our home school group to house everyone's birthdays. I've scoured the forums and can't work out why I'm getting an error on all dates on or after 13/05/2019. All dates prior to this work fine and I just can't figure it out! I also wanted to see if I can incorporate a set_timezone formula to ensure it's based on my Australian timezone. 

Here's what I have set up:

DOB field (members will enter their DOB via a form) and a formula field "Birthday (this year)" with the following formula: 

DATETIME_PARSE(DATETIME_FORMAT({DOB},"DD/MM")&"/"&YEAR(NOW()))
 
Questions:
1. I'm getting an ERROR for any DOBs that are from 13/05/2019 onwards. 
2. Do I need to add a SET_TIMEZONE formula to set the timezone to Brisbane, Australia to ensure the 'NOW' portion of the above formula will be correct throughout the year? 
3. If I wanted to add another field titled "Birthday (next year)" how would I add 1 year to the above formula?
 
Thanks so much for your help!
1 Solution

Accepted Solutions
TheTimeSavingCo
18 - Pluto
18 - Pluto

Hm, it looks like your formula's missing the formatting bit.   Could you try:

DATETIME_PARSE(
  DATETIME_FORMAT(
    {DOB},
    "DD/MM"
  ) & 
  "/" & 
  YEAR(NOW()),
  "DD/MM/YYYY"
)

---
re: 2. Do I need to add a SET_TIMEZONE formula to set the timezone to Brisbane, Australia to ensure the 'NOW' portion of the above formula will be correct throughout the year? 

I believe the "SET_TIMEZONE" bit will only affect the display, and shouldn't affect how `NOW()` works

---
re: 3. If I wanted to add another field titled "Birthday (next year)" how would I add 1 year to the above formula?

Add a `DATEADD()` to it, so like:

DATEADD(
  DATETIME_PARSE(
    DATETIME_FORMAT(
      {DOB},
      "DD/MM"
    ) & 
    "/" & 
    YEAR(NOW()),
    "DD/MM/YYYY"
  ),
  1,
  'years'
)

See Solution in Thread

4 Replies 4
TheTimeSavingCo
18 - Pluto
18 - Pluto

Hm, it looks like your formula's missing the formatting bit.   Could you try:

DATETIME_PARSE(
  DATETIME_FORMAT(
    {DOB},
    "DD/MM"
  ) & 
  "/" & 
  YEAR(NOW()),
  "DD/MM/YYYY"
)

---
re: 2. Do I need to add a SET_TIMEZONE formula to set the timezone to Brisbane, Australia to ensure the 'NOW' portion of the above formula will be correct throughout the year? 

I believe the "SET_TIMEZONE" bit will only affect the display, and shouldn't affect how `NOW()` works

---
re: 3. If I wanted to add another field titled "Birthday (next year)" how would I add 1 year to the above formula?

Add a `DATEADD()` to it, so like:

DATEADD(
  DATETIME_PARSE(
    DATETIME_FORMAT(
      {DOB},
      "DD/MM"
    ) & 
    "/" & 
    YEAR(NOW()),
    "DD/MM/YYYY"
  ),
  1,
  'years'
)

Thank you so much Adam, that worked perfectly!!

chatri
4 - Data Explorer
4 - Data Explorer

Can you sent me the sample table for birthday?

This gives me the birthdays 1 day behind