Help

Management of Birthdates

Topic Labels: Dates & Timezones
4653 6
cancel
Showing results for 
Search instead for 
Did you mean: 
Baljit_Chhabra
5 - Automation Enthusiast
5 - Automation Enthusiast

I have recorded birthdates ie day month year. The birthdates are in the past. How do I see the birthdates in the calendar for the dates ahead.

Thanks in anticipation.

Baljit

6 Replies 6

There are a couple threads that already have solutions to this problem. If you searched before posting, I’m guessing you searched for “birthdate” instead of “birthday,” which would explain why you didn’t find them. :slightly_smiling_face: I hope that something in these threads will give you what you want.

Thank you Justin Barrett,

Oh! Yes, thanks for the input, indeed ‘birthday’ has a many inputs.

Oh! but concatenate functions is a barrier for me as of now. I could not figure out what to do.

Any way is there any function in Airtable where the birthday ( or that matter any recurring date) appear indefinitely for the future years ?

So, I am yet at square one? Birthday

Thanks again Justin Barrett

Baljit

If you want a birthday for upcoming years, you need a new record for each year. Now, that doesn’t mean that you need an indefinite number of records. You could have one record for “this year” and another record for “next year”.

Assuming that the birthdate is stored in a date field in the same record, this formula will give the anniversary of the birthday in the current year.

DATETIME_PARSE(
  DAY({Birthdate}) & "-" & 
  MONTH({Birthdate}) & "-" &
  YEAR(TODAY()),
  "D-M-YYYY"
)

And this one will give it for next year:

DATETIME_PARSE(
  DAY({Birthdate}) & "-" & 
  MONTH({Birthdate}) & "-" &
  (YEAR(TODAY()) + 1),
  "D-M-YYYY"
)

If you want to calculate the “next” birthday after today, use this formula:

IF(
  DATETIME_FORMAT({Birthdate}, "MMDD") > DATETIME_FORMAT(TODAY(), "MMDD"),
  DATETIME_PARSE(
    DAY({Birthdate}) & "-" & 
    MONTH({Birthdate}) & "-" &
    YEAR(TODAY()),
    "D-M-YYYY"
  ),
  DATETIME_PARSE(
    DAY({Birthdate}) & "-" & 
    MONTH({Birthdate}) & "-" &
    (YEAR(TODAY()) + 1),
    "D-M-YYYY"
  )
)

Things get a bit more complicated if your birthdate is in a lookup field instead of the same record as the formula.


If this answers your problem, could you please mark this post as the solution?
If not, could you please give a bit more details on your use case and a screen capture?

The formula above from @kuovonne will do the trick. If the current date is before the birthday (or other date), it will show when that date will fall in the current year; otherwise it will show where it falls next year.

FWIW, here’s an optimized version of that same formula. Because the only difference in those parsing parts is how much we’re adding to the current year, we can wrap the IF() function just around that part, returning either 0 or 1 appropriately.

DATETIME_PARSE(
	DAY({Birthdate}) & "-" & 
  	MONTH({Birthdate}) & "-" &
  	(YEAR(TODAY()) + IF(
  		DATETIME_FORMAT({Birthdate}, "MMDD") > DATETIME_FORMAT(TODAY(), "MMDD"),
  		0, 1)
  	),
  	"D-M-YYYY"
)

Thank you Justin Barrett,

Frankly, this formula is a barrier to me for now and therefore just dropping the idea. For now I will fill in the birthdates and once every year (say 01 Jan) change all the dates to current year.

I was wondering, could Airtable include the function in the Airtable software. So we could just have a field ‘birthday’ and select the date field/ V look up the birthdate field and then ‘birthday’ will auto fill the cell with the birthday. Further when ‘birthday’ transferred to calendar, it will populate the calendar for no. of years defined by the operator.

Thanks

Baljit

Would you mind explaining what you mean when you say that the formula is “a barrier” for you? Is the formula still not doing what you want, or is it something else? I don’t understand the problem.

I’m also unclear what you mean here. It sounds like you want the automatically-updating birthday/anniversary behavior to be a built-in feature. Is that correct? If so, I don’t feel that it’s a good use of the development team’s resources. That only solves a vary narrow problem, and they tend to focus their energies on adding features that have a more broad application.

Because Airtable’s calendar view operates on the dates in specific records, that would mean adding one record for each year for each birthday you want to track. If you say you want to track the next 5 years of birthdays of 100 people, that’s 500 records, versus having a single field in those 100 contact records that always shows when their next birthday will be. While the extra records are certainly doable (it could even be done now using a custom script in the Scripting block), I’m again not convinced that it’s a wise use of resources to add a feature to Airtable just for that purpose. Aside from the development time it would eat up, what would you do with old birthday records from past years? For certain bases, I want to keep a history, but I don’t see the value in doing that for birthdays, and I don’t want to take more of my own time to clean out old records.