Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Jun 06, 2023 10:13 AM
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#
Solved! Go to Solution.
Jun 06, 2023 12:00 PM
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
Jun 06, 2023 12:17 PM
I used what you provided, modified it a little bit and it finally worked. Appreciate it, I simplified it as well.
Jun 06, 2023 10:40 AM
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:
Jun 06, 2023 10:45 AM - edited Jun 06, 2023 11:17 AM
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.
Jun 06, 2023 12:00 PM
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
Jun 06, 2023 12:17 PM
I used what you provided, modified it a little bit and it finally worked. Appreciate it, I simplified it as well.
Jun 06, 2023 12:21 PM
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