Workaround for this use of the COUNTIF function

[user9658457988]

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.

[bernie654]

---

@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:

Excel

=TEXT(DATETIME_FORMAT({Date of Referral},"YY")&COUNTIFS({Date of Referral},">="&{Date of Referral},{Date of Referral},"<="&EOMONTH({Date of Referral},0)),"0000")

Explanation:

1. DATETIME_FORMAT({Date of Referral},"YY"): This part extracts the year from the "Date of Referral" column.
2. 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.
3. 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.

[user9658457988]

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?

[AbigailAdams]

Thank you so much for the help.

Spoiler

COUNTIF formulas can be tricky, just like structuring a strong research paper. While technical formulas require careful input, so do academic arguments. A reliable essay writing service at https://ukwritemyessay.com/ can help streamline complex ideas and ensure they’re organized logically. What’s your specific use case for the formula, and do you think a service like this could simplify things for you? ensures customer satisfaction by offering free revisions. If a student needs modifications, the UK Write My Essay service platform allows them to request changes until they are fully satisfied with the final work.