Skip to main content

DATEADD Function

  • February 23, 2016
  • 2 replies
  • 43 views
B

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

B
  • Bernard_Toure
  • Author
  • Known Participant
  • February 26, 2016

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

D
J
  • Justin_Tuley
  • New Participant
  • July 8, 2016

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.

D
Terms & ConditionsAccessibility statement