- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎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
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎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:
- DATETIME_FORMAT({Date of Referral},"YY"): This part extracts the year from the "Date of Referral" column.
- COUNTIFS({Date of Referral},">="&{Date of Referral},{Date of Referral},"<="&EOMONTH({Date of Referral},0)): This part counts the number of records within the same year as the current record.
- ">="&{Date of Referral}: This condition ensures that only records on or after the current record's date are included in the count.
- "<="&EOMONTH({Date of Referral},0): This condition ensures that only records within the same year as the current record are included in the count. EOMONTH({Date of Referral},0) returns the last day of the month for the given date.
- TEXT(..., "0000"): This part formats the concatenated string (year + count) as a 4-digit number with leading zeros.
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 assumes that your "Date of Referral" column is formatted as a date.
- Adjust the cell references in the formula to match the actual location of your data.
This formula should effectively generate the 4-digit code you require.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎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?
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Feb 10, 2025 03:29 AM - edited ‎Feb 20, 2025 03:30 AM
Thank you so much for the help.
