Generating Dates Based on Status

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?

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

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

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?

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.

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

That’s perfect, thank you!!