Skip to main content

So, I have some columns in one of my bases that are complex formulas working something out based on year. These particular formulas are necessary as there is no quick workaround via rollups etc., it's not pretty - and there is something better involving linked records in the works - but they are doing the job for now. 

Each of these columns show the data for one calendar year, and on an interface I used a number element to sum the column. This way a member of my team has a tidy way of seeing numbers for previous years (each year column has its own number element).

They are all exactly the same column, with the same formula (adjusted for dates), with no options for formatting. I've been able to make four number elements using these going back to 2018.

At the start of the year, I duplicated the 2021 column (which has a number element), and adjusted the formula to cover 2022. But this new column is not available to select within the number element Field Summary field list.

Does anyone have any clue why this would be? 

This is still needed to plug the gaps until the linked record solution is up and running, so any thoughts would be much appreciated.

Intruiging! Any chance you could share some screenshots of the formula columns, as well as the available columns to select from when setting up the Interface Number element?

The formula columns are using the formula I mentioned in this post here: Solved: Conditional DATETIMEDIFF - Airtable Community

In terms of formatting, they all show this:

The available columns to select show all of the previous four years except the most recent duplicate column. But they're all exactly the same.

The formula columns are using the formula I mentioned in this post here: Solved: Conditional DATETIMEDIFF - Airtable Community

In terms of formatting, they all show this:

The available columns to select show all of the previous four years except the most recent duplicate column. But they're all exactly the same.


That's really interesting.  With the formula you provided in the other thread, I'm actually unable to use "Sum" for the Number element in the Interface unless I modify your formula to only output numbers

I was also able to select the year 22 for the number element

Here's a link to my the base where I was able to select 22 but not able to sum it up unless I modified your formula; perhaps it'll be useful in some form for comparison?

Wish I could help more!

That's really interesting.  With the formula you provided in the other thread, I'm actually unable to use "Sum" for the Number element in the Interface unless I modify your formula to only output numbers

I was also able to select the year 22 for the number element

Here's a link to my the base where I was able to select 22 but not able to sum it up unless I modified your formula; perhaps it'll be useful in some form for comparison?

Wish I could help more!


So weird! Are you able to paste your modified formula please?

I think I may just have to chalk this up to a random error... motivation to get the other solution up and running pronto!

So weird! Are you able to paste your modified formula please?

I think I may just have to chalk this up to a random error... motivation to get the other solution up and running pronto!


Yeap sure, I've pasted it below!  I basically just modified the last two lines of "", "Error - Check" to show `0` instead, making that formula field output only numbers

I believe you can also duplicate the base to access the formulas too if you'd like

 

IF(

AND(

YEAR({Start Date})=2019,

YEAR({End Date})=2019

),

DATETIME_DIFF({End Date},{Start Date},"days"),



IF(

AND(

YEAR({Start Date})<2019,

YEAR({End Date})>2019

),

DATETIME_DIFF(

DATETIME_PARSE("31 Dec 19"),

DATETIME_PARSE("01 Jan 19"),

"days"

),



IF(

AND(

YEAR({Start Date})=2019,

YEAR({End Date})>2019

),

DATETIME_DIFF(

DATETIME_PARSE("31 Dec 19"),

{Start Date},"days"

),



IF(

AND(

YEAR({Start Date})<2019,

YEAR({End Date})=2019

),

DATETIME_DIFF(

{End Date},

DATETIME_PARSE("01 Jan 19"),

"days"

),

IF(

OR(

YEAR({Start Date})>2019,

YEAR({End Date})<2019

),

0,

0

)

)

)

)

)

 

 

Yeap sure, I've pasted it below!  I basically just modified the last two lines of "", "Error - Check" to show `0` instead, making that formula field output only numbers

I believe you can also duplicate the base to access the formulas too if you'd like

 

IF(

AND(

YEAR({Start Date})=2019,

YEAR({End Date})=2019

),

DATETIME_DIFF({End Date},{Start Date},"days"),



IF(

AND(

YEAR({Start Date})<2019,

YEAR({End Date})>2019

),

DATETIME_DIFF(

DATETIME_PARSE("31 Dec 19"),

DATETIME_PARSE("01 Jan 19"),

"days"

),



IF(

AND(

YEAR({Start Date})=2019,

YEAR({End Date})>2019

),

DATETIME_DIFF(

DATETIME_PARSE("31 Dec 19"),

{Start Date},"days"

),



IF(

AND(

YEAR({Start Date})<2019,

YEAR({End Date})=2019

),

DATETIME_DIFF(

{End Date},

DATETIME_PARSE("01 Jan 19"),

"days"

),

IF(

OR(

YEAR({Start Date})>2019,

YEAR({End Date})<2019

),

0,

0

)

)

)

)

)

 

 


Woohoo, it worked! Had to find all of the "" that I had done.. but is doing the job now. Thanks so much 🙂

Glad I could help!

Reply


Terms & Conditions