Dateadd with a specific time

[Christian_Gabri]

Hello!

I’m having trouble with a formula, DATEADD({date}, 2, ‘days’) works perfectly but I need to add time as well specifically.

For example, a record was created on Saturday at 8pm, the formula should logged a date on Monday at exactly 7am.

We have team A who creates record during weekends and those records are sometimes being created in weekends. Team B will only be able to check those records during Monday to Friday and their scores/metrics are being affected in our report. We wanted to create an automatic field that will logged those records on weekends to Monday at 7am.

I think a formula won’t work on this issue and scripting would be better but I’m not good at coding.

[Ben_Young1]

Hey @Christian_Gabriel_Cr!
Welcome in!

I’m not at my computer right now, so I can’t build an example and provide screenshots, but I can explain it nonetheless.

The thing to know about the DATEADD function is that it supports adding an array of time/date parameters.
So if you were to format the result of the function into a DATETIME_FORMAT function like this:

DATETIME_FORMAT(
    DATEADD(
        NOW(),
        1,
        'days'
    ),
    'LLL'
)

You would get something like this: July 8, 2022 12:00 AM.

Notice that while it went from 7/7/22 to 7/8/22, but the time defaults to midnight.

So, if we know that the time always defaults to midnight on the date, we can use another DATEADD function to set the time for the desired result. This time, we’ll change the parameter from being 'days' to 'hours'.

If we wanted to have the formula go from right now to July 8th, 2022 at 8am, we would use this formula:

DATETIME_FORMAT(
    DATEADD(
        DATEADD(
            NOW(),
            1,
            'days'
        ),
        8,
        'hours'
    ),
    'LLL'
)

The formula should return this: July 8th, 2022 8:00 AM.

---

Depending on how complex your use case is, scripting can be better, but handling date/time values in programming can be frustrating, especially since Airtable doesn’t allow you to import libraries.

Nonetheless…
If that doesn’t work (I’m not able to test it) or if you have any questions, please let me know and I’d be happy to help!

[Christian_Gabri]

Hi Ben! I appreciate the help especially when you’re not at your computer and still provided insight. I did the formula you provided but the NOW() function is always updating in a time interval, so the result of the formula changes as well. There was no error, it’s just that it’s not the result we’re looking for.

To give you a better insight of what we’re trying to accomplish, here’s the result with the formula you provided.

I replaced the NOW() with a date field but still not giving me the result we want.

This is the formula with the date field instead of the NOW() function.

IF(
  {Weekday}='Sunday',
  DATETIME_FORMAT(
    DATEADD(
      DATEADD(
        {Calculation 4},
        1,
        'days'
      ),
      7,
      'hours'
    ),
    'LLL'
  )
)

Here’s the formula that we’re trying to fix in one of our database, we want those records that being created on weekends to logged a date on Monday at 7am.

IF({Week Day}="Sunday",CREATED_TIME({Date and Time Received}),IF({Week Day}="Saturday",CREATED_TIME({Date and Time Received}),IF({Week Day}="Monday",DATEADD({Date and Time Received},0,'days'),IF({Week Day}="Tuesday",DATEADD({Date and Time Received},0,'days'),IF({Week Day}="Wednesday",DATEADD({Date and Time Received},0,'days'),IF({Week Day}="Thursday",DATEADD({Date and Time Received},0,'days'),IF({Week Day}="Friday",DATEADD({Date and Time Received},0,'days'))))))))

The problem with the formula is if Weekday=“Saturday” or “Sunday”, it’s giving as the created date and time instead. Our goal is whenever a record is being created during Saturday and Sunday, those records will logged a date on Monday at 7am no matter what time the records was created during weekends.

Thank you so much for looking into this, I appreciate it.

[Ben_Young1]

@Christian_Gabriel_Cr

In the original formula I provided, I only used the NOW() function as a placeholder.
My bad for not clarifying its purpose!

Since I’ve been back in front of my computer, I’ve realized that I was wrong about the behavior of the DATEADD function.

It does not set the date/time value with an automatically set time of 12:00:00 AM.

It just adds 24 hours to the date/time value you provide it.

I’m going to play with this for a bit and return with some more information.