Help

Trying to add a looked-up value and DATEADD

5796 19
cancel
Showing results for 
Search instead for 
Did you mean: 
Duncan_Fischer
5 - Automation Enthusiast
5 - Automation Enthusiast

I am trying to a similar thing, but instead of adding an INTERGER for the multiplier in the DATEADD formula, I am trying to pick up a value form a table LOOKUP.

Have also tried with a FORMULA cell and it doesn.t work either.

I have different categories of appointments to book, that each have different duration and priorities

TIA,
Duncan

19 Replies 19

It’s the same, you just change the source of the value from a number to the name of your field:

DATEADD({Date Field}, {Lookup Field}, Unit).

I’ve tried various fields and they don’t compute.

This works, but i need to look up a number of minutes from the separate table of APPT TYPE.

Screen Shot 2018-12-23 at 7.41.11 pm.png

Duncan

You’re right, I’m testing it and I am getting weird behaviour:

  • If I make a formula like Lookup + 5, I get a number, so the Lookup field acts like a number.
  • If I use it in the DATEADD function it does not work :dizzy_face:
  • Using VALUE(Lookup) does not work
  • The only way to get it to work is summing with a 0: DATEADD(Date, Lookup + 0, 'days'). Note the + 0.

I think this is a #bugs to note to @Airtable_Support

thanks, that worked. I guess it is a simple bug
D

A naked Lookup is returned as an array — and predicting how Airtable will treat an array when used in a formula is definitely a black art. Fortunately, you can always cast a Lookup into a string by appending ‘&''’ – that is, an ampersand followed by two quotes signifying an empty string — which can then be used in VALUE() or similar functions.

You could also define {Appt Finish} as a Rollup field using an aggregation function. To do so, configure it as a Rollup that follows the link given in {Appt type} to roll up the {Nbr of Minutes} [or whatever you call it] field. Then, in the place where you would ordinarily enter an aggregation function, enter

DATEADD(
    {Appointment Time},
    VALUE(values&''),
    'minutes'
    )

values is a keyword indicating Airtable should use the rolled up value of the specified remote field. It, too, is returned as an array, so it has to be cast as a string and then converted to a number using VALUE().

Why might you want to do this? Well, in this case you aren’t saving very much processing cost or eliminating much complexity. However, it does reduce your field count by one, in that you no longer need explicitly to look up the ‘minutes’ value and then use it in a formula field, as the remote reference and the formula both take place in the same field.

Thanks so much @W_Vann_Hall … only had to change the minutes to seconds as the original table outputs seconds it seems. One less column to squeeze on the screen.

D

Any thoughts on…
I have, in some cases, multiple collaborators that can perform a certain appt type, in the {Appointment Book} i want to limit a field to allow only ONE collaborator to be selected dependent on the appt type.

Screen Shot 2018-12-25 at 5.22.50 pm.png Screen Shot 2018-12-25 at 5.23.44 pm.png

As far as I know, nothing in Airtable supports such granularity regarding collaborators. (Actually, Airtable currently isn’t big on conditional choices of any kind.) As a work-around, I’ll sometimes implement alerting on invalid choice combinations; while it’s still possible to specify a bad combination, an alert is raised to the user indicating something is wrong and needs to be fixed.

I define the alert field as the second field in the table — that is, the one immediately following the primary field. This places the alert at the top of any expanded record for increased visibility. It’s possible to use a single alert field to reflect multiple error conditions — even ones passed from other tables. (For an example, take a look at the [garment] table in my Wardrobe Manager base. Choose the <Development (all fields)> view in [garment]. You can hover your cursor over the :information_source: to show field descriptions for formula fields; I include the formula config in the description, so you don’t need to copy the base just to check the formulas.) Typically, I’ll use both emoji-based conditional coloring in the field itself along with (for Pro-level bases) record-level conditional color.

Again, not a perfect solution, but at least one that increases the likelihood invalid data entry will be caught and corrected at the time the error is made.

And even if you aren’t significantly reducing processing load in this instance, the use of aggregation formulas in Rollup fields is a good trick to keep in mind because at times it can greatly streamline computation-intensive Lookup references. The case I most often site is that of my data deduplication routines: Switching to an aggregation formula-based algorithm for version 2 seemingly cut overhead on a 1,000-record table by the equivalent of four copies of War and Peace as compared with version 1…

Hi,

DATEADD( ThatDate, VALUE(lookup value), ‘day’) shows #ERROR but DATEADD( ThatDate, lookup value +0, ‘day’) works for me.

Cheers

Fred

Larrissa_Cunnin
4 - Data Explorer
4 - Data Explorer

Hi,

I am trying to something similar I think, I have tried all of the above but not working

Screen Shot 2020-06-26 at 10.58.59 am

I am trying to add a unique “feed frequency” to auto calculate the “Next feed date” and using the DATEADD formula is not working for me.

This is what I was trying: DATEADD({Last Feed Date}, {Feed Frequency}+0, ’day’)

Any suggestions?

Just after I posted this I found the answer over here:

Until the bug is solved, that should be stated in the help articles themselves! That info shouldn’t be here and here alone.

BTW, how’s that bug solving going? It’s been at least a year and a half already?

I don’t work at Airtable :man_shrugging:

I know. But I thought that, since Airtable support has already been mentioned and the big is real, AT staff would be actively participating in this thread.

Naive me?

Such a strange bug, but glad the workaround works. Any chance that this is going to be addressed?

Ruslan_Romaniuc
4 - Data Explorer
4 - Data Explorer

Hi there!

I try to use DATEADD to calculate customer “Due Payment Date” based on the order “Ship Date” and picking up values (LOOKUP) from another table since I work with various payment credit terms: 30d, 45d, 50% in 30d / 50% in 60d…

I use positive Integer numbers for the payment terms (30, 45…) and the formula works fine:

DATEADD({Ship Date},VALUE(ARRAYJOIN({Terms 1}),‘days’))

However I have cases with split payment terms like 50% before Ship Date and 50% in 30 days after Ship Date. As said above the formula for “after Ship Date” works with positive Integer lookup values, and I’m struggling to make the same formula work for the advance payment (50% before the Ship Date) - I tried to use in the Lookup table negative Integer number for advance payments. But the problem is the #ERROR in the DATEADD formula whenever I use zero or negative value in the Lookup table instead of positive integer.

Can anyone help please?

Also if anyone knows a way how to manage due dates for split payment terms (for ex. 30/60/90/120 days for one order) without creating separate columns for each payment term and % value it would be of great help!

@Elias_Gomez_Sainz @W_Vann_Hall can you please suggest if there’s any solution to my need?
Thank you

thank you for the solution! I was getting crazy other this simple “bug”.