Skip to main content
Solved

Birthday Calendar - recurring date that automatically updates each year

  • March 30, 2023
  • 4 replies
  • 80 views
M
Forum|alt.badge.img+7

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!

Best answer by TheTimeSavingCo

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' )
RonniEloff_VKDe
MarcoConnex

4 replies

TheTimeSavingCo
Forum|alt.badge.img+31

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' )
Adam, Airtable Consultant - thetimesaving.company
M
M
Forum|alt.badge.img+7
  • Meagan_Caesar
  • Author
  • Known Participant
  • April 2, 2023

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!!

TheTimeSavingCo
C
C
Forum|alt.badge.img+1
  • chatri
  • New Participant
  • April 28, 2023

Can you sent me the sample table for birthday?

J
Forum|alt.badge.img+3
  • jmshade
  • New Participant
  • December 29, 2023

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' )

This gives me the birthdays 1 day behind

M
Terms & ConditionsAccessibility statement