Automatic due dates based on single select category

[Bri_Field]

I’m trying to create automatic due dates for video production timelines. We have different production timelines for different videos - ads and standard videos with a 5 week turnaround time, short form videos with a 1 week turnaround time, and instant videos with a 2 day turnaround time.

I want to be able to select a final due date for the video and for the production due dates to calculate automatically based on the type of video.

I’ve sort of figured it out - my problem is that the formatting is very difficult to read, and I can’t change the formatting. Here’s what I have:

I’d like the due dates to look like a regular date without the time, but it won’t let me choose the formatting when I add the IF statements.

Any suggestions? Thanks!

[Ben_Young1 · Accepted solution]

Hey @Bri_Field!
Welcome to the forums!

Here’s a formula I wrote that should satisfy your requirements:

IF(
    AND(
        {Video Type},
        {Final Due Date}
    ),
    IF(
        OR(
            {Video Type} = "Ad/Lifestyle",
            {Video Type} = "Standard Site"
        ),
        DATETIME_FORMAT(
            DATEADD(
                {Final Due Date},
                -4,
                'weeks'
            ),
            'l'
        ),
        IF(
            {Video Type} = "Short Form",
            DATETIME_FORMAT(
                DATEADD(
                    {Final Due Date},
                    -3,
                    'days'
                ),
                'l'
            ),
            IF(
                {Video Type} = "Instant",
                DATETIME_FORMAT(
                    DATEADD(
                        {Final Due Date},
                        -1,
                        'days'
                    ),
                    'l'
                )
            )
        )
    ),
    'Error'
)

That formula returns this behavior:

---

If you’re curious as to why you couldn’t format your formula output.

Bri_Field:

I can’t change the formatting

So, you aren’t able to change the formatting because your formula has the possibility of returning a string.

Here’s the formula you posted:

1  IF(
2     {Video Type} = "Ad/Lifestyle",
3    DATEADD(
4       {Final Due Date},
5      -4,
6     'weeks'
7      ),
8      IF(
9          {Video Type} = "Standard Site",
10          DATEADD(
11              {Final Due Date},
12              -4,
13            'weeks'
14          ),
15          IF(
16              {Video Type} = "Short Form",
17              DATEADD(
18                  {Final Due Date},
19                  -3,
20                  'days'
21              ),
22              IF(
23                  {Video Type} = "Instant",
24                  DATEADD(
25                      {Final Due Date},
26                      -1,
27                      'days'
28                  ),
29                  "Error"
30              )
31          )
32      )
33  )

On line 29 of this block, you’re telling Airtable to return the string "Error" if nothing returns true on your nested IF functions. However, everything else before that can only ever return a date value.

To better explain the difference, here’s what the formula produces if we remove the "Error" part of the formula.

So, since our formula will only ever return a date value, we can format those possible values as you were expecting.

Records 5 & 6 don’t return anything because the Video Type field is blank.

Record 7 returns an #Error value because whilst the Video Type field meets one of the parameters, there isn’t a date value for the DATEADD function to use, so it’s functionally the same as using a simple DATEADD function on an empty field like this:

DATEADD(
    {Final Due Date},
    1,
    'days'
)

---

The solution here is to ensure that every value the formula could return is a string.
The DATETIME_FORMAT function will return a string with the added benefit of allowing you to format the output however you’d like.
More information about the function and formatting can be found here.

[Bri_Field]

This worked perfectly, thanks!