Help

Re: Automatic due dates based on single select category

Solved
Jump to Solution
517 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Bri_Field
4 - Data Explorer
4 - Data Explorer

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:

image

image

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.

image

Any suggestions? Thanks!

1 Solution

Accepted Solutions
Ben_Young1
11 - Venus
11 - Venus

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:

image


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

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.

image

image

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'
)

image


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.

See Solution in Thread

2 Replies 2
Ben_Young1
11 - Venus
11 - Venus

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:

image


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

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.

image

image

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'
)

image


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.

This worked perfectly, thanks!