Nesting formulas to create due dates

[laurel_groh]

Hi.

I’m trying to create a formula that will leave a cell blank if certain qualifications aren’t met and, if they are met, to complete an additional formula and have that answer register as a date and I’m stuck.

More specifically - I am creating a database where I can enter project deadlines and then work back from there to create additional due dates. for example, I have an marketing piece that needs to be launched 10/27/18. I then have 3 other fields that are lookup fields that pull in “lead times” based on the type of marketing piece.

Where I’m running into difficulty is with the subsequent formula fields. Wherever a look up column is a 0 and/or blank, I want the formula field to remain blank, but if there is a number in it, I want it to create a formula that results in a date field. So, for example:

Thanksgiving Cards - Launch date is 11/16/2018. I want to create a “to printer” field that subtracts 4 days from the launch date to create a new “to printer field” that shows a due date of 11/09/18 and leaves the others blank

The formula I’ve been trying is IF ({To printer}=0," ",(DATEADD({Launch Date},SUM({To printer}),‘days’))). It works - but the date field is a long integer and not a formatted date.

When I try to use a date format formula, everything breaks down. Either I end up with dates that aren’t formatted as dates (I think they’re seen as text) or an error.

Can anyone help?

thanks in advance!
Laurel

[Jeremy_Oglesby]

I think you may be overcomplicating your formula a bit there.

You’ll want to use BLANK() instead of an empty string "" as the IF=0 value, because the empty string tells Airtable that you want this to be a field with the “text” data type, and this will preclude any other data types (like a date).

You also appear to have some extraneous parenthesis in there, and I’m not sure what the purpose of the SUM() function is.

If I am seeing your setup properly, I’m fairly certain this should work:

IF(
   {To Printer} = 0,
   BLANK(),
   DATEADD(
      {Launch Date},
      {To Printer},
      'days'
   )
)

I presume you have separate date fields for all the other milestone deadlines you have there, so the same pattern could be followed for those as well.

Let me know if that doesn’t work or if you need to expand on it.

[Elias_Gomez_Sai]

And I don’t know why you don’t get a date :grinning_face_with_sweat:

[laurel_groh]

Jeremy_Oglesby:

IF( {To Printer} = 0, BLANK(), DATEADD( {Launch Date}, {To Printer}, ‘days’ ) )

Thank you - that worked.

[laurel_groh]

thank you both for your time, help and explanations! I really appreciate it!

A little off the subject, but since you both mentioned the SUM function: I originally added it because of this question:article The answer from Matt Bush was that

The problem you’re encountering is that a Lookup field is an array of numbers, not a number itself. The reason it’s an array is that if there are multiple linked records, the Lookup field needs to be able to hold multiple numbers>

I have several of these columns that Lookup fields - and when I was creating the original DATEADD for those columns (not using the SUM function), I got error messages. When I followed his suggestion, it worked. None of those columns are blank, but I assumed that the logic still held. It obviously didn’t and the simpler approach worked much, much better (and more logically) - but do you know why it would be different?

thank you both again!

[Jeremy_Oglesby]

Matt Bush appears to be referring to a Lookup field that could potentially hold more than one number, since it is Looking up on a cell that has more than one linked record.

In your case, your Lookup fields are only looking up a single value, so that value is being properly formatted as a number. If a Lookup field found that the field it looked at returned more than one number value, it would be forced to format them as an “Array” of values - that is, a list of strings (not numbers, but key characters that represent numbers) separated by commas. This is just a standard database convention. But like I said, in your case, your Lookup fields are only retrieving a single number value, so the database knows that it can simply format it as a number, and you don’t have to perform the SUM() operation on it to force it into being a number.