The Community will be undergoing maintenance from Friday February 21 - Friday, February 28 and will be "read only" during this time. 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.