The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.
Feb 02, 2025 08:45 AM
Hi all,
I need to generate a 4 digit code from the year a record was created and how many records have come before in that year.
E.g the 5th record that has been created in 2023 = 2305.
I have reformatted the referral date
Feb 03, 2025 04:11 AM
@user9658457988 wrote:Hi all,
I need to generate a 4 digit code from the year a record was created and how many records have come before in that year.
E.g the 5th record that has been created in 2023 = 2305.
I have reformatted the referral date
DATETIME_FORMAT({Date of Referral},"YY") but unable to get a COUNTIF function to work to count how many referrals have been made before this one where the referral date is in the same year.
Here's the formula to generate the 4-digit code:
=TEXT(DATETIME_FORMAT({Date of Referral},"YY")&COUNTIFS({Date of Referral},">="&{Date of Referral},{Date of Referral},"<="&EOMONTH({Date of Referral},0)),"0000")
Explanation:
Example:
If the "Date of Referral" for a record is "2023-07-15" and there are 5 records with referral dates in 2023 up to and including July 15th, the formula will generate the code "2305".
Note:
This formula should effectively generate the 4-digit code you require.
Feb 03, 2025 04:52 AM
Thanks for the explanation!
Air table doesn't support countifs, eomonth or text functions so this formula doesn't work. Do you have a work around for these functions?
Feb 10, 2025 03:29 AM - edited Feb 20, 2025 03:30 AM
Thank you so much for the help.