Formula Assistance

[Michael_Hjorten]

Question on the following formula.  I have tried just about anything and everything to get this formula field to only calculate the score based on after the date.  The formula works perfectly fine without using any date fields. 

Form type (single choice option), LH_GC_Def_1 (checkbox) field.   

Current working formula for field GC#

IF({Form Type}="QCLH",

IF(LH_GC_Def_1=1,

0,

3))+

IF({Form Type}="VOICE",

IF(LH_GC_Def_1=1,

0,

2))+

IF({Form Type}="QCLH",

IF(LH_GC_Def_2=1,

0,

3))+

IF({Form Type}="VOICE",

IF(LH_GC_Def_2=1,

0,

2))

If I try to add in the date field to the GC# column (date of session field) using an operator, it looks like some portions are scored correctly and some are completely wrong, like it is only counting a portion.

I have also tried to use IS_AFTER but the formula fails to work, or pulls up an ERROR!. 

Any thoughts?

[Stephen_Orr1]

Hi @Michael_Hjorten,

In your third screenshot, the dates you've supplied need to be adjusted to be understood as dates. You're not seeing an error here b/c Airtable is reading these as 5 divided by 20 divided by 2023.

Try replacing them with:

DATETIME_PARSE('5/20/2023')

DATETIME_PARSE() converts a text based date into an actual date that Airtable can use in calculations.

Hope that helps!
-Stephen

[Michael_Hjorten]

Hi @Stephen_Orr1  I gave that a try and still didn't work.  Wasn't sure as that field within AirTable is a date column field type.  I tried to add the formula below and it goes through but the scoring is almost like it is avoiding a portion of the fields to get the exact scoring. 

IF({Form Type}="QCLH",

IF({LH_GC_Def_1}=1,

IF({Date of Session}<DATETIME_PARSE('5/20/2023'),

0,

3)))+

IF({Form Type}="VOICE",

IF({LH_GC_Def_1}=1,

IF({Date of Session}<DATETIME_PARSE('5/20/2023'),

0,

2)))+

IF({Form Type}="QCLH",

IF({LH_GC_Def_2}=1,

IF({Date of Session}<DATETIME_PARSE('5/20/2023'),

0,

3)))+

IF({Form Type}="VOICE",

IF({LH_GC_Def_2}=1,

IF({Date of Session}<DATETIME_PARSE('5/20/2023'),

0,

2)))

Outcome: Any records with LH_GC_Def_2 = checked the score comes to 3 with records after 5/20/2023 (Which is correct).  However all records after 5/20/2023 with neither Def1 or Def2 checked should = 6 total points (3 per).  This is the same results I keep receiving.  If I remove any fields associated with the date, the scoring formula works perfectly fine.   

[Stephen_Orr1 · Accepted solution]

I reformatted your formula a bit

IF({Form Type} = "QCLH",
     IF(AND({LH_GC_Def_1}, {Date of Session} > DATETIME_PARSE('5/20/2023')),
          3,
          0
     )
     +
     IF(AND({LH_GC_Def_2}, {Date of Session} > DATETIME_PARSE('5/20/2023')),
          3,
          0
     ),
     0
)
+
IF({Form Type} = "VOICE",
     IF(AND({LH_GC_Def_1}, {Date of Session} > DATETIME_PARSE('5/20/2023')),
          2,
          0
     )
     +
     IF(AND({LH_GC_Def_2}, {Date of Session} > DATETIME_PARSE('5/20/2023')),
          2,
          0
     ),
     0
)

 You could definitely simplify this further to reduce duplicate logic but I left it as-is in case you plan on adjusting the numbers in the future.

-Stephen

[Michael_Hjorten · Accepted solution]

Hi @Stephen_Orr1 

I used what you provided, modified it a little bit and it finally worked.  Appreciate it, I simplified it as well.

IF(AND({Form Type} = "QCLH",{Date of Session} > DATETIME_PARSE('5/20/2023')),

IF({LH_GC_Def_1}=1,

0,

3

)

+

IF({LH_GC_Def_2}=1,

0,

3

))

+

IF(AND({Form Type} = "VOICE", {Date of Session} > DATETIME_PARSE('5/20/2023')),

IF({LH_GC_Def_1}=1,

0,

2

)

+

IF({LH_GC_Def_2}=1,

0,

2

))

[Stephen_Orr1]

Awesome and glad to help! As a favor, please fee free to mark any of my replies as the solution in this thread if I was helpful. I would really appreciate it!

Thanks,
-Stephen