Help: Find MIN() Date for each month in a date time field & dateadd -28 weeks

[Joe_Svingala]

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')

[Kamille_Parks · Accepted solution]

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

1. Create a formula field that gets the month and year for the {Air Date} field. i.e. IF({Air Date}, DATETIME_FORMAT({Air Date}, "YYYY-MM"))
2. Create a link field for a (new) table for Months.
3. Copy the whole formula column from step 1 into the link field from step 2. (You can set up an automation to copy/paste values automatically in the future)
4. In the Months table create a rollup field of the {Air Date} using the MIN(values) aggregation.
5. In the original table create a lookup field of the rollup field from step 4.
6. Set your {Work Order Due} formula to be: IF({Lookup Field}, DATEADD({Lookup Field}, -28, "weeks"))

Option 2

1. See step 1 from Option 1
2. Set up a view to group by that field
3. Follow this example from the BuiltOnAir podcast on how to use a grouped view to “rank” values in a View: [S09-E07] Full Podcast for 10-26-2021 [RAW ORIGINAL VIDEO] - YouTube
   The improved script mentioned in that episode was copied here:
   Comparing records in different fields in the same table - #2 by Kamille_Parks
4. Edit that script to fill in a “Min Air Date” field using the first item from the group array.
5. Base the {Work Order Due} field formula off of the {Min Air Date} field.

[Joe_Svingala]

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.