Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

How to configure a conditional, combined DATETIME_DIFF formula

Topic Labels: Formulas
Solved
Jump to Solution
2179 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Aysia_Saylor
6 - Interface Innovator
6 - Interface Innovator

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:

1 Solution

Accepted Solutions
Kamille_Parks
16 - Uranus
16 - Uranus

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.

See Solution in Thread

2 Replies 2
Kamille_Parks
16 - Uranus
16 - Uranus

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.

This is great, thank you!