Simple DATEADD with duration?

[wadenn]

Why won’t this work? I feel like I’m following instructions to no avail. Tried every possible combo with the duration field, same too long result that I can’t calculate. Field 12 is where I’m trying to get the result of for example Date 5:30 pm, with run time of 120 minutes, would then result in same date 7:30pm.

The fields are as follows Date and Start Time, Run Time, Duration, 60, Duration as seconds.

1/23/2020 5:30pm 1:42 102 60 6120

I’ve calculated run time as seconds, used that - I love airtable but some simple functions are tough.

Here’s the formula (or one of them) I thought would work.

DATEADD({Date and Start Time}, {Run time}, ‘seconds’)

[Justin_Barrett]

Welcome to the community, @! :grinning_face_with_big_eyes: By default, the formatting for a formula field that outputs a date is to display the date based on GMT, not your local timezone. This is probably why it feels like the calculation is too long. After fixing the formatting, it displays the correct time for me when using the final formula from your post:

DATEADD({Date and Start Time}, {Run Time}, "seconds")

[wadenn]

That’s it! Thank you!

[Les]

Hi Justin,
I had a similar problem and did the above thank you. However, what happens for example if I only work a 12 hour shift I have my date and start time as 1/23/2020 16:30 and a run time of 4:00hrs and our shifts finish at 1800hrs. How do I get it to recognise this and then change the end time to the next day 1/24/2020 0830 ?
thanks
les

[Justin_Barrett]

There’s some inconsistency in the data you’re listing in your example. A 12-hour shift starting at 16:30 would end at 04:30 the next day, not 08:30. The “run time” in the original example was the run time for an event/activity, so a 4-hour run time in your case would change the end time to 20:30. I’m not sure where the 18:00 hours reference comes into play, but it sounds like you want the 4-hour duration to somehow add to your overall 12-hour shift, so you have a 12-hour shift beginning at 16:30, plus 4 hours of ???, which takes you to 08:30 the next day. Is that correct? Can you provide more details about your use case so I have a more clear picture of what you’re trying to accomplish?

[Les]

Hi Justin,
Our shifts operate 0600 to 1800 each day.
So if I receive an order request on 1/23/2020 at 1630hrs and the actual manufacturing time to do that order is 4hrs for example, I need to record the finish time as being the following day at 0830 and not 2030 the same night. I only have 1.5hrs left of the shift on the 1/23/2020 so i need to carry over the other 2.5 hours to the following morning.
“so you have a 12-hour shift beginning at 16:30, plus 4 hours of ???, which takes you to 08:30 the next day. Is that correct?” - this is correct.

[Justin_Barrett]

Les:

“so you have a 12-hour shift beginning at 16:30, plus 4 hours of ???, which takes you to 08:30 the next day. Is that correct?” - this is correct.

Actually, that’s not correct, but your preceding paragraph actually explained it clearly enough that I’ve got a better picture of what you want.

Long story short, all you really need to do is add 12 hours to the manufacturing time when calculating the end time if the initial calculation puts you past 1800. Here’s what I came up with:

The {End of Day} field calculates the end of whatever day the job started, using this formula:

DATETIME_PARSE(DATETIME_FORMAT({Date and Start Time}, "M/DD/YYYY 18:00"))

The {Initial End Time} formula is the same as what I wrote above. The {Actual End Time} formula is this:

IF({Initial End Time} > {End of Day}, DATEADD({Initial End Time}, 12, "hours"), {Initial End Time})

Hide the helper fields ({End of Day} and {Initial End Time}) when everything is set.

Also keep in mind that {Date and Start Time} and {Initial End Time} MUST be set to use GMT in the field options, or else the calculations won’t work. I didn’t expect it to be this way, but that’s what I found in my tests.

[Les]

Thanks Justin, I have managed to master it now. The thing that got me a little was the way Airtable does the dates. I had mine changed to the UK way (the correct way :slightly_smiling_face: ) , it just meant a little change on a few places.
Thank you very much for your help.

[Ambroise_Dhenai]

Here is a formula for my field plannedEndDate, which is automatically calculated from a plannedAt field (type Date with time).

It calculates the duration of the end of the event, taking into consideration that if it is the first event it will take 90mn, while other events take 60mn.

Before, I wasn’t using DATETIME_FORMAT nor DATETIME_PARSE, and the time was off because it was using the GMT+0 value. The following code works properly and displays my GMT+1 time.

IF(
  LEN(plannedAt & '') > 0,
  IF(
    title = 'Première rencontre',
    DATEADD(
        DATETIME_PARSE(
            DATETIME_FORMAT(
                SET_TIMEZONE(plannedAt, 'Europe/Paris'),
                'YYYY-MM-DD HH:mm'
            ),
            'YYYY-MM-DD HH:mm'
        ),
        90,
        'minutes'
    ),
    DATEADD(
        DATETIME_PARSE(
            DATETIME_FORMAT(
                SET_TIMEZONE(plannedAt, 'Europe/Paris'),
                'YYYY-MM-DD HH:mm'
            ),
            'YYYY-MM-DD HH:mm'
        ),
        60,
        'minutes'
     )
  ),
  BLANK()
)