Generating Dates Based on Status

[Eric_Marlin]

Hello y’all,

I’m writing out a formula that will generate dates. The dates will be based off a different calendar field in my table, and will be adjusted based on a dropdown menu. There are two formulas I’m attempting to use. The first is as follows:

IF({Recording Date},DATEADD({Recording Date},SWITCH({Status},‘Outlined’,-3,‘Script Writing (including editing)’,0,’Review Outline’,-2,’Review Story Draft’,-2,’Ready to be Recorded’,0),‘days’))

With the goal being to adjust the date off of the “Recording Date” based off the “Satus” (the drop down menu). This has worked well. However, when I tried a similar approach for a column called “Upload Date,” the formula doesn’t work. Here it is:

IF({Upload Date},DATEADD({Upload Date},SWITCH({Status},‘Recorded and Ready to be Edited’,-5,’Requests for Re-records’,-3,’Sound Edited and Ready for Review’,-2,’Complete and Ready to be Uploaded’,0),’days’))

Do you by any chance see an error in how I’ve written out this second formula?

[Justin_Barrett]

What exactly is the problem you’re encountering? The basic syntax of the formula looks good, although you want to make sure that you’re using straight quotes instead of curly quotes (i.e. ' vs '). If you copied all or part of the formula from this forum, and the original wasn’t formatted as code, then the forum will use curly/styled quotes around your strings, which will throw an error. I’ve fixed the quotes below; let me know if that solves it:

IF(
    {Upload Date},
    DATEADD(
        {Upload Date},
        SWITCH(
            {Status},
            'Recorded and Ready to be Edited',-5,
            'Requests for Re-records',-3,
            'Sound Edited and Ready for Review',-2,
            'Complete and Ready to be Uploaded',0
        ),
        'days'
    )
)

[Eric_Marlin]

Oh that did seem to work! My other question then is if I wanted the formula to include both of these formulas I’ve written (both the dates based off the "Record Date’ and the dates based off the “Upload Date,” should I simply list them both separated by a comma. for example:

IF(
{Upload Date},
DATEADD(
{Upload Date},
SWITCH(
{Status},
‘Recorded and Ready to be Edited’,-5,
‘Requests for Re-records’,-3,
‘Sound Edited and Ready for Review’,-2,
‘Complete and Ready to be Uploaded’,0
),
‘days’
)
),
IF(
{Recording Date},
DATEADD(
{Recording Date},
SWITCH(
{Status},
‘Outlined’,-3,
‘Script writing (including editing)’,0,
‘Review Outline’,-2,
‘Review Story Draft’,-2,
‘Ready to be Recorded’,0
),
‘days’
)
)

[Justin_Barrett]

That syntax won’t work. There is a way to merge both IF() functions into one, depending on how you want them to relate to each other, but the merged formula will still only affect the contents of a single field: the one in which it lives. Do you really want one date to change based off of what’s happening in two other fields? What exactly are you trying to achieve?

[Eric_Marlin]

The goal is for it to live in a single field, and for this one date to change based off what’s happening in two other fields. What I want is to generate a date based on what’s in this “Status” field. The issue is that depending on which item has been selected in the “status” column, sometimes I need to calculate the date in relation to “Recording Date,” and sometimes I need to calculate it in relation to “Upload Date.” The goal is for these dates to populate in the “Current Task Due Date” column.

[Justin_Barrett]

Doing this in a single field is possible, though it would be pretty messy IMO. Frankly, I find it easier to tackle using two formulas instead of one. Make a field named something like {Date and Offset} using this formula:

SWITCH(
    Status,
    "Outlined", {Recording Date} & "-3",
    'Script writing (including editing)', {Recording Date} & " 0",
    'Review Outline', {Recording Date} & "-2",
    'Review Story Draft', {Recording Date} & "-2",
    'Ready to be Recorded', {Recording Date} & " 0",
    'Recorded and Ready to be Edited', {Upload Date} & "-5",
    'Requests for Re-records', {Upload Date} & "-3",
    'Sound Edited and Ready for Review', {Upload Date} & "-2",
    'Complete and Ready to be Uploaded', {Upload Date} & " 0"
)

That creates a mashed-together version of the raw date and offset amount for each status option. Then your {Current Task Due Date} formula field extracts and parses the pieces:

IF(
    {Date and Offset},
    DATEADD(
        DATETIME_PARSE(
            LEFT({Date and Offset}, 24)
        ),
        VALUE(RIGHT({Date and Offset}, 2)),
        "days"
    )
)

[Eric_Marlin]

That’s perfect, thank you!!

[BSS_Automation]

Hi Justin,
I’m trying to do the same thing but don’t have a huge amount of experience and can’t seem to get mine to work.

my columns are “Deposit Day” and “# Days Advised” - depending on which drop down selected, it is +30 days, 35 days, 40 days and 45 days.
Using your formula I got this far but it doesn’t seem to work for me.

IF(
{Deposit Day},
DATEADD(
{Deposit Day},
SWITCH(
{# Days Advised},
‘30 Days’,+30,
‘35 Days’,+35,
‘40 Days’,+40,
‘45 Days’,+45
),
‘days’
)
)

My result column is “Expected ETA”.
I’d appreciate any help you could give me.

[Justin_Barrett]

@BSS_Automation Drop the “+” in front of the output numbers. The function name is “DATEADD”, implying that the value for the second argument will be added to the current date. In other words, a positive number is all you need. You only need to specify a sign if the number is negative. Your formula should look like this:

IF(
    {Deposit Day},
    DATEADD(
        {Deposit Day},
        SWITCH(
            {Status},
            '30 Days', 30,
            '35 Days', 35,
            '40 Days', 40,
            '45 Days', 45
        ),
        'days'
    )
)

Another way is to extract the value from the beginning of the {Status} output, but only if you can guarantee that the first two characters of that field will always be numbers representing that offset:

IF(
    {Deposit Day},
    DATEADD(
        {Deposit Day},
        VALUE(LEFT({# Days Advised}, 2)),
        'days'
    )
)

EDIT: Corrected field reference.

[BSS_Automation]

Copied and pasted your formula and it came back with an error. Tried by removing the “Days” and leaving just the numbers but still didn’t work