Oct 28, 2021 01:10 PM
Hello,
What would be the easiest way to configure a formula that combines all of these?
IF({Request Type}=‘New’, DATETIME_DIFF({DCML Issue Date Lookup},RequestDate,‘days’)
IF({Request Type}=‘Major Update (Procedure Change)’, DATETIME_DIFF({DCML Rev Date Lookup},RequestDate,‘days’)
IF({Request Type}=‘Minor Update (Formatting, Spelling, Grammar, etc.)’,DATETIME_DIFF({DCML NCC Date},RequestDate,‘days’)
IF({Status}=‘Incomplete / Closed’, DATETIME_DIFF({Status Last Modified Date},RequestDate,‘days’)
I tried a formula that combined the first two, and the system accepts it, but it doesn’t return anything.
Thanks :slightly_smiling_face:
Solved! Go to Solution.
Oct 28, 2021 01:53 PM
I would use a combination of SWITCH() and IF() statements for this:
SWITCH(
{Request Type},
"New", IF(
AND({DCML Issue Date Lookup}, {RequestDate}),
DATETIME_DIFF({DCML Issue Date Lookup}, {RequestDate}, "days")
),
"Major Update (Procedure Change)", IF(
AND({DCML Rev Date Lookup}, {RequestDate}),
DATETIME_DIFF({DCML Rev Date Lookup}, {RequestDate}, "days")
),
"Minor Update (Formatting, Spelling, Grammar, etc.)", IF(
AND({DCML NCC Date}, {RequestDate}),
DATETIME_DIFF({DCML NCC Date}, {RequestDate}, "days")
),
"Incomplete / Closed", IF(
AND({Status Last Modified Date}, {RequestDate}),
DATETIME_DIFF({Status Last Modified Date}, {RequestDate}, "days")
)
)
The above formula will test the {Request Type}
and depending on its value output the relevant difference between two dates only if both those date fields have a value.
Oct 28, 2021 01:53 PM
I would use a combination of SWITCH() and IF() statements for this:
SWITCH(
{Request Type},
"New", IF(
AND({DCML Issue Date Lookup}, {RequestDate}),
DATETIME_DIFF({DCML Issue Date Lookup}, {RequestDate}, "days")
),
"Major Update (Procedure Change)", IF(
AND({DCML Rev Date Lookup}, {RequestDate}),
DATETIME_DIFF({DCML Rev Date Lookup}, {RequestDate}, "days")
),
"Minor Update (Formatting, Spelling, Grammar, etc.)", IF(
AND({DCML NCC Date}, {RequestDate}),
DATETIME_DIFF({DCML NCC Date}, {RequestDate}, "days")
),
"Incomplete / Closed", IF(
AND({Status Last Modified Date}, {RequestDate}),
DATETIME_DIFF({Status Last Modified Date}, {RequestDate}, "days")
)
)
The above formula will test the {Request Type}
and depending on its value output the relevant difference between two dates only if both those date fields have a value.
Oct 28, 2021 02:10 PM
This is great, thank you!