Skip to main content

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

I am completely new to airtable 🙂 I just tried this out in what I think is a similar situation and am getting error message unfortunately…

 

I am trying to create a unique code for the number of seed collections (by species) that includes the year. So if we had three collections of the same species in the same year, it would say 2025-3.

 

 

This is also somewhat complicated by the fact that we will have seed we collect ourselves and seed we purchase… Right now I am working on developing two separate tables for seed sources. Maybe I need to finish those first before I create the “Seed Record ID”

 

As mentioned above, Airtable doesn’t support COUNTIF (or EOMONTH). There’s a handy dandy Formula Playground base available in the support docs (which I would link to but the Support Center seems down right now...)

Anywho, the other thing to consider is that generally speaking formula fields only engage with data inside their own record (row). Dunno if it’s helpful to frame like this, but formulas only move horizontally not vertically. So the way this is set up in the screenshot, there's only one date it’s pulling all that information from, and even if that date is repeated in other rows, it’s not going to tally those together. The way you do something like that is to utilize a new table and a linked record field for collection dates, and then you can lookup/rollup that information to utilize for formulas. 

I think this link should work to get to the formula playground.

https://airtable.com/appCHsByjT6czDeJu/shrHgArBZ61sNOtKl/tblkwFqAgvq4M5tp6/viwAD5P14cSxKntDm?blocks=bipdCFHjZaYzAsFzd

I’ve found it very useful.  

This question comes up several time a year, and I’ve seen a lot of interesting “solutions”, but aside from scripting I don’t think there is an easy solution.  What ​@DisraeliGears01 is saying is pretty much the approach I took, then decided that it’s probably better to just combine the year with an autonumber field.

As mentioned above, Airtable doesn’t support COUNTIF (or EOMONTH). There’s a handy dandy Formula Playground base available in the support docs (which I would link to but the Support Center seems down right now...)

Anywho, the other thing to consider is that generally speaking formula fields only engage with data inside their own record (row). Dunno if it’s helpful to frame like this, but formulas only move horizontally not vertically. So the way this is set up in the screenshot, there's only one date it’s pulling all that information from, and even if that date is repeated in other rows, it’s not going to tally those together. The way you do something like that is to utilize a new table and a linked record field for collection dates, and then you can lookup/rollup that information to utilize for formulas. 

 

This is so helpful! thanks ​@BillH and ​@DisraeliGears01 

 

That was my hunch when I was working on this and posted the question. Somehow I missed that detail  about the formulas in the post above. I did see the formula playground link earlier and appreciate knowing about it!! I’ve only been at this about a week and am still on the steep part of the learning curve.

Ah and now Support Center is working again for me. Thanks ​@BillH for coming through with the link! I’ve copied Formula Playground into my workspace and use it for reference quite often, highly recommended. 

As Bill mentioned too, you might be able to hack something together via automations and scripting, but that’s beyond me. Just figured that’d be some helpful insight as it sounds like your still assembling your bases and workspace. 

For what it’s worth, a simple automation that should work if you are always entering the data in the current year.

Add a field for the number - format it with no decimals.  If you don’t have a date field for the entry make one or use the created date field.

For the Automation

Trigger - When a new record is created.

Action One - Find all records the the date your using falling in the current year.

Action Two - Update the created record from the trigger with the count of records from the Find Records step.

Find Record step example
Update Record Example

 

Reply


Terms & Conditions