Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Feb 18, 2022 04:12 PM
I know that I am probably approaching this formula wrong, or maybe it isn’t possible with the way MIN()/Dates/Fields work in Airtable - but I figured I would ask the community for a little help on this one.
Context: Need to assign work order due dates based off content Air Date. More specifically, I am trying to take the earliest date found for each month then subtract 28 weeks (dateadd -28 weeks), and use that Min Date - 28 weeks as the Work Order Due Date for every record that has an air date with the month that calculated it.
Example:
Record | Air Date | Work Order Due
Record 1 | 04/22/2023 | 9/19/2022
Record 2 | 04/14/2023 | 9/19/2022
Record 3 | 04/03/2023 | 9/19/2022
Record 4 | 04/19/2023 | 9/19/2022
Record 5 | 07/22/2023 | 12/29/2022
Record 6 | 07/25/2023 | 12/29/2022
Record 7 | 07/13/2023 | 12/29/2022
In the example above, the four records have an Air Date in April, but the formula would calculate the Work Order Due Date as 28 weeks prior from 4/03/2023 since its the minimum value in April - additionally, the records with an Air Date in July have a work order due date of 12/29/2022 which would be 28 weeks prior to the MIN date found for July air dates.
The formula that I thought would work was a riff off of what I would have done in Excel, but with some finessing due to the MIN issues with Date fields - ultimately it doesn’t work, it just re-inserts the airdate associated with each record, but I am hoping there may be someone here who has some experience with doing something similar. Thank you!
Formula:
DATEADD IS NOT INCLUDED, I don’t need assistance with that portion.
DATETIME_PARSE(
MIN(
IF(MONTH({Air Date})=1, VALUE(DATETIME_FORMAT({Air Date},'YYYYMMDD')),
IF(MONTH({Air Date})=2, VALUE(DATETIME_FORMAT({Air Date},'YYYYMMDD')),
IF(MONTH({Air Date})=3, VALUE(DATETIME_FORMAT({Air Date},'YYYYMMDD')),
IF(MONTH({Air Date})=4, VALUE(DATETIME_FORMAT({Air Date},'YYYYMMDD')),
IF(MONTH({Air Date})=5, VALUE(DATETIME_FORMAT({Air Date},'YYYYMMDD')),
IF(MONTH({Air Date})=6, VALUE(DATETIME_FORMAT({Air Date},'YYYYMMDD')),
IF(MONTH({Air Date})=7, VALUE(DATETIME_FORMAT({Air Date},'YYYYMMDD')),
IF(MONTH({Air Date})=8, VALUE(DATETIME_FORMAT({Air Date},'YYYYMMDD')),
IF(MONTH({Air Date})=9, VALUE(DATETIME_FORMAT({Air Date},'YYYYMMDD')),
IF(MONTH({Air Date})=10, VALUE(DATETIME_FORMAT({Air Date},'YYYYMMDD')),
IF(MONTH({Air Date})=11, VALUE(DATETIME_FORMAT({Air Date},'YYYYMMDD')),
IF(MONTH({Air Date})=12, VALUE(DATETIME_FORMAT({Air Date},'YYYYMMDD')),""))))))))))))),'YYYYMMDD')
Solved! Go to Solution.
Feb 18, 2022 04:43 PM
Your formula is doing the same calculation for every record for each month of the year. Since a date can only belong to one month, its asking “what’s the min of just the date and no other comparison values”, which of course would just be the date. Parsing that out would return you to where you started (the Air Date).
Also formulas can’t “see” records that aren’t linked to it. You would need to (1) use an Automation to link records together so you could use Rollup field to find the minimum date, or (2) run a script to find the smallest date in the table (for each month).
Option 1
{Air Date}
field. i.e. IF({Air Date}, DATETIME_FORMAT({Air Date}, "YYYY-MM"))
MIN(values)
aggregation.{Work Order Due}
formula to be: IF({Lookup Field}, DATEADD({Lookup Field}, -28, "weeks"))
Option 2
{Work Order Due}
field formula off of the {Min Air Date}
field.Feb 18, 2022 04:43 PM
Your formula is doing the same calculation for every record for each month of the year. Since a date can only belong to one month, its asking “what’s the min of just the date and no other comparison values”, which of course would just be the date. Parsing that out would return you to where you started (the Air Date).
Also formulas can’t “see” records that aren’t linked to it. You would need to (1) use an Automation to link records together so you could use Rollup field to find the minimum date, or (2) run a script to find the smallest date in the table (for each month).
Option 1
{Air Date}
field. i.e. IF({Air Date}, DATETIME_FORMAT({Air Date}, "YYYY-MM"))
MIN(values)
aggregation.{Work Order Due}
formula to be: IF({Lookup Field}, DATEADD({Lookup Field}, -28, "weeks"))
Option 2
{Work Order Due}
field formula off of the {Min Air Date}
field.Feb 21, 2022 08:19 AM
Thank you Kamille,
Very much appreciate the well thought out response. I had suspected that my field reference was returning the record’s MIN date, returning just the date referenced for each individual record - still grasping a few of the airtable nuances in contrast to Excel/GSheets formulas.
Both of these are incredibly helpful & will most likely approach it from Option 1 until I spend some more time in the scripting realm.