Skip to main content
Solved

How to configure a conditional, combined DATETIME_DIFF formula

  • October 28, 2021
  • 2 replies
  • 33 views
A
Forum|alt.badge.img+8

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:

Best answer by Kamille_Parks11

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.

2 replies

Kamille_Parks11
Forum|alt.badge.img+27

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.

A
Forum|alt.badge.img+8
  • Aysia_Saylor
  • Author
  • Known Participant
  • October 28, 2021

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!

Terms & ConditionsAccessibility statement