DATETIME_FORMAT not formatting when used with existing formula

Rachel_Erickson · ‎Aug 10, 2021

Hi folks,

I cannot figure out why this formula is not outputting what I need. My current formula for the column is this:

IF({ETA (AMZ) Date}="",“Need ETA (AMZ) Date”,DATEADD({ETA (AMZ) Date}, (7 - (WEEKDAY({ETA (AMZ) Date}) - 2)),‘days’))

If there is a date in the ETA AMZ DATE column, mark as Need ETA (AMZ) Date. If there is a date, create new date + 7 days from existing date and to the nearest Tuesday.

When I use this formula, the output looks like this: 2021-01-26T00:00:00+00:00

SO, I’ve tried DATETIME_FORMAT

IF({ETA (AMZ) Date}="",“Need ETA (AMZ) Date”,DATETIME_FORMAT(DATEADD({ETA (AMZ) Date}, (7 - (WEEKDAY({ETA (AMZ) Date}) - 2)),‘days’)),‘MM/DD/YYYY’)

When I save the formula, it automatically removes the ‘MM/DD/YYYY’ leaving me with

IF({ETA (AMZ) Date}="",“Need ETA (AMZ) Date”,DATETIME_FORMAT(DATEADD({ETA (AMZ) Date}, (7 - (WEEKDAY({ETA (AMZ) Date}) - 2)),‘days’)))

This doesn’t solve my problem. What am I missing? How can I add the format formula to my existing formula to populate the date as MM/DD/YYYY?

Kamille_Parks · ‎Aug 10, 2021

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.

Rachel_Erickson · ‎Aug 10, 2021

ahhh duh ok thank you! I think I needed another set of eyes, thanks!!

Rachel_Erickson · ‎Aug 11, 2021

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?

Kamille_Parks · ‎Aug 11, 2021

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

Rachel_Erickson · ‎Aug 11, 2021

this seems to have almost solved my problem:

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

I’ll do a bit of tweaking for my specific errors but your answers helped me SO MUCH, thank you.