Help

Birthday Calendar - recurring date that automatically updates each year

Topic Labels: Dates & Timezones Formulas
Solved
Jump to Solution
3246 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