Setting the date condition (last calendar year) in...

[Meagan_Caesar]

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!

[TheTimeSavingCo · Accepted solution]

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

[Meagan_Caesar]

Thank you so much Adam, that worked perfectly!!

[chatri]

Can you sent me the sample table for birthday?

[jmshade]

This gives me the birthdays 1 day behind