WORKDAY with IF and DATEADD, pulling from multiple fields?

[Theodora_Hart]

Hello! I’m able to make this formula work but I keep getting errors when I try to insert ‘WORKDAY’ into it:

IF({Original Release Date}, DATEADD({Original Release Date}, VALUE(’’ & {Days Needed Before Release}), ‘days’, ‘’))

I am trying to return the closest workday after subtracting a formulated number of days from my original release date.

Thanks!

[Kamille_Parks]

Your formula appears to have quotation marks and parenthesis in the wrong places. I believe your formula should look like:

IF(
{Original Release Date}, 
DATEADD({Original Release Date}, VALUE(""&{Days Needed Before Release}), "days")
)

Leaving out the “” in the if false position prevents Airtable from reading the format of the column as a string and not a date. If your values aren’t already negative, include a - in front of VALUE.

Adding in WORKDAY would then look like:

IF(
{Original Release Date}, 
WORKDAY(DATEADD({Original Release Date}, VALUE({Days Needed Before Release}) + 1, "days"), -1)
)

^ I’m adding one day to the DATEADD because I am assuming {Days Needed Before Release} is a negative number and because WORKDAY needs to add (in this case subtract) at least one day to function.

[Justin_Barrett]

Theodora_Hart:

I’m able to make this formula work but I keep getting errors when I try to insert ‘WORKDAY’ into it:

Aside from what @Kamille_Parks mentioned, there’s one other possible source of the error. The WORKDAY() function doesn’t accept a units argument like DATEADD() does; i.e. you actually need to omit the “days” string if you replace “DATEADD” with “WORKDAY” in the formula that you listed. This is because the WORKDAY() function is designed to only work with days and no other units. (Some of Airtable’s functions will accept extra arguments and not complain about it, but WORKDAY() isn’t one of them.)

[Kamille_Parks]

Theodora_Hart:

I am trying to return the closest workday after subtracting a formulated number of days from my original release date

@Justin_Barrett I took that to mean they need both DATEADD and WORKDAY to get what they need

[Theodora_Hart]

Thanks, Both! Yes @Kamille_Parks is correct that I’m trying to incorporate both DATEADD and WORKDAY in the same formula, and unfortunately it looks like @Justin_Barrett is also correct in that WORKDAY and DATEADD don’t play well together because @Kamille_Parks’ proposed solution still returns an error…

Most of what I need I can achieve with this simple formula:

WORKDAY({Original Release Date},{Days Needed Before Release})

Except that I am also trying to also avoid returning errors when either of those fields are blank. The best I can come up with is 2 formulas; the one above and then a second (IF) formula to fix the #Error’s because of blanks (which then leads to the additional issue of needing to reformat the date returned)

I hope that made sense. Any suggestions here?

[Kamille_Parks · Accepted solution]

If you’re only getting errors because some of your inputs are blank, adjust the IF() clause to account for that:

IF(
AND({Original Release Date}, {Days Needed Before Release}),
WORKDAY(DATEADD({Original Release Date}, VALUE({Days Needed Before Release}) + 1, "days"), -1)
)

or

IF(
AND({Original Release Date}, {Days Needed Before Release}),
WORKDAY({Original Release Date},{Days Needed Before Release})
)

[Theodora_Hart]

Kamille_Parks:

IF(
AND({Original Release Date}, {Days Needed Before Release}),
WORKDAY(DATEADD({Original Release Date}, VALUE({Days Needed Before Release}) + 1, "days"), -1)
)

Clarifying the original formula you suggested as the solution doesn’t work at all (Errors in every cell). I think due to what @Justin_Barrett said about WORKDAY not accepting units as DATEADD does?

[Kamille_Parks]

Notice how the formula I gave doesn’t have a unit argument for WORKDAY. It has a number argument, which is required.

I tested the formula in my own base and got no errors, can you post a screenshot of yours? (relevant formula field is {Workday Test})

[Justin_Barrett]

@Kamille_Parks My bad for the misread.

@Theodora_Hart WORKDAY() and DATEADD() can work together as long as the requirements of each function are met. The “days” unit string in Kamille’s formula is part of the DATEADD() function, not WORKDAY().

I’ll second Kamille’s request for a screenshot. That will probably help us find the problem more quickly.

[Theodora_Hart]

The second version of the formula you offered works! Thank you so much! Life saver!