Trouble with duration portion of DATEADD function

[Dean_Arnold]

Hi there,

I’m trying to use the DATEADD function to calculate an end time for an event.

My formula is:
DATEADD({Timeslot StartTime},{Duration mins},‘minutes’)

{Duration mins} can be a lookup field or a formula field. I’ve tried all formatting options for these fields but none are producing the correct output.

If I make {Duration mins} a text field, the output is correct.

I need to make the formula work with {Duration mins} being either a lookup field or a formula ~ What am I doing wrong?

P.S. I have also tried to troubleshoot the issue by toggling the GMT settings on and off, all to no avail.

Thanks for your help.

[ScottWorld]

@Dean_Arnold

{Duration mins} can be a lookup field or a formula field.

What do you mean by this? A field can only be one type.

[Dean_Arnold]

Hi Scott,

Thanks for your reply. Yes, that is why I said in my post that in my formula, {Duration mins} can be (i.e. can represent) a lookup field or a formula field.

In other words, instead of using a static numeric value in that portion of the DATEADD function, I want to pull the number from either a lookup field or a formula field. However, neither is working for some reason, despite all the different formatting options I’ve tried.

My formula only works if
a) I type in a numeric value for the duration part of the formula, or
b) I pull the duration value from a text or number field.

I need to pull the duration from a lookup field or a formula field.

Hope you can help. Thanks.

[ScottWorld]

As long as: (a) your lookup field results in a single number only (not multiple numbers), or (b) your formula field ONLY results in a number without any possible text strings as the result of your formula, then you should be able to use either of those fields as your duration.

You can tell if your lookup field results in a number field by customizing the lookup field and clicking on the “formatting” tab. If it doesn’t give you number formatting options there, then your lookup field is not resulting in a number. (You can do the same thing with formula fields, too.)

If you think that your lookup field or formula fields are resulting in text, then you could try using the function VALUE({Field Name Here}), which will extract the numbers from your text field.

Also, if it’s not adding up the times correctly, maybe double-check to make sure both the original date field & your final formula field are both set to the same exact GMT setting (either on or off).

[Justin_Barrett]

@ScottWorld hit all of the important points, but I’d to dig a little deeper if that’s okay.

What is the formula that you’re trying to use? I’ve used formulas to drive stuff like this all the time, so it really comes down to the specific formula you wrote and how it’s designed. Seeing that will let us know why the formula isn’t working, and allow us to guide you towards a fix.

For lookup fields, if you’re only looking up a single value from a single linked record, it should work. It’s when the lookup pulls in multiple values via multiple linked records that it could go awry because it’ll create a number array, not just a single number. I built a table recently to help spotlight stuff like this with lookup fields because the results aren’t consistent across field types.

Airtable Field Reference - Airtable

[Dean_Arnold]

Thanks, Scott, Jason,

None of the potential causes you mention explain my issue.

My formula is:
DATEADD({Timeslot StartTime},{Duration mins},‘minutes’)

This image shows the configurations for all fields at play:

As you can see, GMT options are toggled off and the values are formatted as integer numbers.

The correct output for the ‘Timeslot EndTime’ field in the first row should be: 1 March 2021 12:30am.

In my formula, If I replace {Duration mins} with 30, that is what I get.

Are we in bug territory?

Thanks!

[ScottWorld]

Thanks for the screenshots — very helpful!

There’s something strange going on here, because your lookup field “Duration Mins” is justified left instead of justified right. Number fields are always justified right, including lookup fields that lookup numbers.

So it’s strange that it gives you the formatting options for a number field, but then it is justifying your field left like a text field. The left justification indicates that it thinks the values in your field are text strings instead of numbers.

I don’t know the answer to why it’s doing that, so I would email support@airtable.com, and please report back here with what they tell you.

It‘s very possible that you have stumbled upon a bug of some sort… you might try deleting and recreating the lookup field from scratch and see what happens.

In the meantime, you can try using the VALUE() function as I mentioned earlier. In your formula, use VALUE({Duration Mins}) so that it extracts the number from the text.

See if that fixes it as a workaround. Although even if that fixes it, you shouldn’t have to do that, so I would still email support and let us know what they say.

[Dean_Arnold]

Thanks, Scott.

I tried using VALUE({Duration Mins}) but no dice. I can save the formula field but the Output gives me an error. I’ve emailed support.

[Justin_Barrett]

@Dean_Arnold Thanks for the details. I just ran a test on this, and found the solution. I’ve also updated my field reference table with my findings.

When using a single looked-up value in a simple mathematical calculation, Airtable will apparently auto-convert the lookup array into a single number, and in my initial tests when building my reference table, that’s all that I tried. However, in this situation where the lookup field is inserted as an argument into a function, it’s not doing that, so it’s still an array. That also explains why wrapping VALUE() around it doesn’t work: VALUE() only works with strings. You’ll need to first convert the array to a string by concatenating it with an empty string, and then VALUE() will behave as expected.

Your new formula should look like this:

DATEADD({Timeslot StartTime}, VALUE({Duration mins} & ""), 'minutes')

[ScottWorld]

Wow, good sleuthing, @Justin_Barrett! Thanks for figuring out & posting that solution!!

[Dean_Arnold]

Thanks for following up!

Peter from Airtable support found an alternate solution:

DATEADD({Timeslot StartTime},ARRAYJOIN({Duration mins}),‘minutes’)

Here is his fully reply:

"I believe I’ve fixed the issue by “wrapping” the reference to the lookup field with an ARRAYJOIN() fuction, which converts the Lookup field from an array to a string (which in turn makes it interpretable by the DATEADD() function).

Totally get that this is not intuitive – I actually raised this very same issue to our product team a few weeks back, and am going to use your message as an opportunity to do so again."

[Justin_Barrett]

Thanks for sharing what you got from Peter. Another function (that totally slipped my mind) that’s a little more intuitive is the SUM() function. Normally it’s used to add a collection of values, but it works just as well when passed a single value in an array.

DATEADD({Timeslot StartTime}, SUM({Duration mins}), 'minutes')