Help

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.

Using COUNTIF statements

Topic Labels: Formulas
305 3
cancel
Showing results for 
Search instead for 
Did you mean: 
user9658457988
4 - Data Explorer
4 - Data Explorer

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.
3 Replies 3
bernie654
4 - Data Explorer
4 - Data Explorer

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

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
4 - Data Explorer
4 - Data Explorer

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.