DATETIME_FORMAT not formatting when used with existing formula

Your formula provides mixed outputs. A field in Airtable can’t output a “date” for some records and a “string” (aka text) for others. When a formula conditionally returns multiple types of outputs, Airtable always defaults to “string”. That’s why your initial format outputs 2021-01-26T00:00:00+00:00, its an ISO timecode or in other words what a “date” value looks like as a “string”.

Your revised formula is missing a closing parenthesis. A corrected formula, simplified slightly, would be:

IF(
   {ETA (AMZ) Date}, 
   DATETIME_FORMAT(
      DATEADD({ETA (AMZ) Date}, 7 - WEEKDAY({ETA (AMZ) Date}) - 2, "days"), 
      "MM/DD/YYYY"
   ),
   "Needs Date"
)

Keep in mind that if any field relies on the above formula, it may not recognize the dates as “dates” and you will not be able to sort by this column properly based on your chosen date format.

Your formula provides mixed outputs. A field in Airtable can’t output a “date” for some records and a “string” (aka text) for others. When a formula conditionally returns multiple types of outputs, Airtable always defaults to “string”. That’s why your initial format outputs 2021-01-26T00:00:00+00:00, its an ISO timecode or in other words what a “date” value looks like as a “string”.

Your revised formula is missing a closing parenthesis. A corrected formula, simplified slightly, would be:

IF(
   {ETA (AMZ) Date}, 
   DATETIME_FORMAT(
      DATEADD({ETA (AMZ) Date}, 7 - WEEKDAY({ETA (AMZ) Date}) - 2, "days"), 
      "MM/DD/YYYY"
   ),
   "Needs Date"
)

Keep in mind that if any field relies on the above formula, it may not recognize the dates as “dates” and you will not be able to sort by this column properly based on your chosen date format.

Okay, this has not been resolved after all.

I’m struggling to create a successful formula for taking a {event date} adding 7 days and then rounding up to the nearest Tuesday. This is what I have right now, which I know is incorrect :

IF({ETA (AMZ) Date},DATEADD({ETA (AMZ) Date},(7 - WEEKDAY({ETA (AMZ) Date}-2)), “days”),“Needs ETA (AMZ) Date”)

My issue stems from the Tuesday part. WEEKDAY({ETA (AMZ) Date}-2)),

If i’m reading this right, this is going to the previous Tuesday? How can I change this to following Tuesday?

That depends on the weekday of the {ETA (AMZ) Date}. Weekdays are numbered 0 - 6, where Sunday is 0 and Saturday is 6. Finding the next or previous “Tuesday” means the number you add or subtract will depend on the difference between the original date’s weekday and “Tuesday”. If the original date isn’t always Thursday, then the number will not always be 2.

You say adding, but you’re substracting from 7 and then subtracting 2 afterward. Assuming your formula should output the date of the first Tuesday after {ETA (AMZ) Date}:

DATEADD(
   {ETA (AMZ) Date}, 
   SWITCH(
      WEEKDAY({ETA (AMZ) Date}), 
      0, 2, 
      1, 1, 
      2, 0, 
      3, 6, 
      4, 5, 
      5, 4, 
      6, 3
   ), 
   'days'
)

If you want to do the Tuesday following a date that is already a Tuesday, change: 2, 0 to 2, 7