Help

DATEADD Function

Topic Labels: Formulas
3873 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Bernard_Toure
6 - Interface Innovator
6 - Interface Innovator

Hi,
Trying to add Minutes to a DateTime seems not to work when the minutes comes from another column.
Col1 : DateTime
Col2: Duration in Decimal (ie: 3.5 for 3h30m)
Col3: DateAdd(Col1,Col2*60,‘minutes’)

Result: Col3 = Col1

If instead of using Col2 in the second parameter, I use a constant value (ie:210), then it works.
If I create a Col4 as a formula of Col2*60 and then use Col4 as the second parameter of DateAdd, then it’s also not working.

Am I doing something wrong?

Cheers,
Berni

2 Replies 2
Bernard_Toure
6 - Interface Innovator
6 - Interface Innovator

Hi,
I discovered that the issue comes from the fact that the duration is the result of a lookup.
If the duration is part of the same table as the date belongs to, then it works.
Scenario to reproduce the bug:
Table1-Col1 : DateTime
Table1-Col2: Lookup of Table2-Col1: Duration in Decimal (ie: 3.5 for 3h30m)
Col3: DateAdd(Table1-Col1,Table1-Co2*60,‘minutes’)

I come up with a solution by using a numeric function like MIN in the formula.
Scenario that fix the issue:
Table1-Col1 : DateTime
Table1-Col2: Lookup of Table2-Col1: Duration in Decimal (ie: 3.5 for 3h30m)
Table1-Col3: DateAdd(Table1-Col1,MIN(Table1-Col2)*60,‘minutes’)

Cheers,
Berni

Justin_Tuley
4 - Data Explorer
4 - Data Explorer

This worked great for my vegetable production where I want to auto calculate # of days in the greenhouse based on my initial start date.

DateAdd({Seed Date},{Days in Transplant},‘day’) did not work because {Days in Transplant is based on a reference table called Varieties. It returned the same date as my seed date and didnt calculate.

Adding in the nested MIN did the trick!

DateAdd({Seed Date},MIN({Days in Transplant}),‘day’)

Now if I can get rid of the timestamp.