Skip to main content

I am trying to add a specific duration to a datetime format, then reformat the output to another datetime.


So for example:


Datetime Field 1 / duration field (h:mm) / Formula Output (datetime format)


I want to take datetime field 1 and add [duration field] to make a datetime format.


Any way to do this?

DATEADD({DateTime Field 1}, {Duration Field}, 'seconds')

The Duration field outputs its value as a number in “seconds” to any formula field. You can check this by making a “Duration” field, then making a “Formula” field that just returns the value of the “Duration” field.


If you Duration field had a value of 1:00 (ie, 1 hour), the Formula field would return a value of 3600, which is the number of seconds in an hour.

DATEADD({DateTime Field 1}, {Duration Field}, 'seconds')

The Duration field outputs its value as a number in “seconds” to any formula field. You can check this by making a “Duration” field, then making a “Formula” field that just returns the value of the “Duration” field.


If you Duration field had a value of 1:00 (ie, 1 hour), the Formula field would return a value of 3600, which is the number of seconds in an hour.


Ah that explains why when I put minutes it did not work.


Thanks!

Can anyone explain what i’m doing wrong? I’m trying to add a duration to a date, but I’m just getting the original date back.


DATEADD({start}, {duration}, ‘seconds’)


with {start} set to “1/1/2000 7:00pm” and {duration} 3:00 (aka 10800 seconds), this formula is returning 1/1/2000 7:00pm, i.e. the exact same start time.


any ideas? thanks!

Can anyone explain what i’m doing wrong? I’m trying to add a duration to a date, but I’m just getting the original date back.


DATEADD({start}, {duration}, ‘seconds’)


with {start} set to “1/1/2000 7:00pm” and {duration} 3:00 (aka 10800 seconds), this formula is returning 1/1/2000 7:00pm, i.e. the exact same start time.


any ideas? thanks!


Welcome to the Airtable community!


Could you provide a bit more information?



  • What is the Duration Format for the duration field?

  • How is the Use the same time zone (GMT) for all collaborators toggle set for the start date/time field?

  • How is the Use the same time zone (GMT) for all collaborators toggle set for the formula field?

Welcome to the Airtable community!


Could you provide a bit more information?



  • What is the Duration Format for the duration field?

  • How is the Use the same time zone (GMT) for all collaborators toggle set for the start date/time field?

  • How is the Use the same time zone (GMT) for all collaborators toggle set for the formula field?


thanks kuovonne for your reply!



  • duration format i’ve set to h:mm (and it was initially set to integer, so i’ve tried that too)

  • both the other fields are set to use GMT for all collaborators.


the duration field is actually a lookup from another table, and i’ve noticed that if i directly write a number of seconds in the DATEADD, it does work, but the lookup doesn’t. is that not supported?

thanks kuovonne for your reply!



  • duration format i’ve set to h:mm (and it was initially set to integer, so i’ve tried that too)

  • both the other fields are set to use GMT for all collaborators.


the duration field is actually a lookup from another table, and i’ve noticed that if i directly write a number of seconds in the DATEADD, it does work, but the lookup doesn’t. is that not supported?


Ah, the duration field actually being a lookup field is the source of your problem. Lookup fields do not play nicely with formulas. A lookup field returns an array, not a simple number.


Here are some workarounds:



  • Convert the lookup field to a rollup field with an aggregation formula that returns a simple number (MIN, MAX, or AVERAGE). or

  • Use a formula to convert the lookup to a number: VALUE({lookup field} & "")

Ah, the duration field actually being a lookup field is the source of your problem. Lookup fields do not play nicely with formulas. A lookup field returns an array, not a simple number.


Here are some workarounds:



  • Convert the lookup field to a rollup field with an aggregation formula that returns a simple number (MIN, MAX, or AVERAGE). or

  • Use a formula to convert the lookup to a number: VALUE({lookup field} & "")


i used the second method, and it works! thanks so much

Ah, the duration field actually being a lookup field is the source of your problem. Lookup fields do not play nicely with formulas. A lookup field returns an array, not a simple number.


Here are some workarounds:



  • Convert the lookup field to a rollup field with an aggregation formula that returns a simple number (MIN, MAX, or AVERAGE). or

  • Use a formula to convert the lookup to a number: VALUE({lookup field} & "")


Thank you for this Kuovonne

Reply


Terms & Conditions