Skip to main content

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

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

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.

Reply


Terms & Conditions