Skip to main content

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!

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'

)

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

Can you sent me the sample table for birthday?

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

Reply


Terms & Conditions