Fixing Dateadd Formula in Airtable to Return Desired Date

Have you ever found yourself stuck with a date formula that just won't behave as expected? This was our problem when we were trying to log records created on weekends and update them to Monday at 7am. We wanted to use a date field instead of the NOW() function, but still weren't getting the desired result.

The original formula didn't produce an error, but it wasn't giving us what we needed either. The issue was that the NOW() function is always updating in real-time, causing the formula's output to change constantly. We wanted a specific date and time, not one that kept changing.

Our goal was to return a date like July 8th, 2022 8:00 AM. Depending on how complex your use case is, scripting may be a better option, but handling dates and times in programming can be frustrating, especially since Airtable doesn't allow library imports.

To achieve our desired result, we needed to modify the formula to set the time for the specific date. We used DATEADD to add hours to the original date, which took us from July 7th, 2022 at midnight to July 8th, 2022 at midnight (12:00 AM).

However, this still didn't give us the desired result of Monday at 7am. To fix this, we used another DATEADD function to set the time for our desired outcome. We added 7 hours to the original date to get the correct time.

The final formula looked like this: DATETIME_FORMAT(DATEADD('hours', 7, DATEADD('days', 1, 'Date Field')), 'MMM D, YYYY h:mm A'). This returned the exact result we were looking for: July 8th, 2022 8:00 AM.

If you're still having trouble getting your date formula to work as expected in Airtable, don't hesitate to reach out. With a little creativity and experimentation, you can achieve the results you need using DATEADD functions and formatting options.

Dateadd airtable