Hi ,
I have two date start date and end date , and i want time duration in days but the problem is data is coming from task report .
a!gridTextColumn( label:"Start Date", field:"c6", data:index(local!dataset.data,"c3",{}) ),
and
a!gridTextColumn( label:"End Date", field:"c8", data:index(local!dataset.data,"c11",{})
),
i need one more column which shows the duration of time for these two date .
please suggest
Discussion posts and replies are publicly visible
What have you tried so far / what exactly is the problem you're having?
i want subtract start date and end date ... to get time in duration .... is it possible
Jun 9, 2019 11:34 AM start date
Aug 6, 2019 11:34 AM end date
I tried this:
datetime(2011, 12, 13, 12, 0, 0) - datetime(2011, 12, 10, 10, 0, 0)
in one of the example boxes, and this was the result:
It's Interval format, 3 days and 2 hours. So the - (minus) operator should take care of it.
Let us know if you have further problems.
If you need to do this in a grid, just reference both of your columns using a!forEach(). Here's an example of how you can set up the column:
a!gridTextColumn( label: "Duration", data: a!forEach( items: local!dataset.data, expression: todecimal(fv!item.c11 - fv!item.c3) ) )
I used the decimal because it will give you a count in days - if you want to format it differently, you might have to parse out the parts of the interval (see davidl's post above mine).
getting this error
Expression evaluation error at function 'todecimal' parameter 1 [line 92]: Cannot sub incompatible operands of type Any Type and type Any Type.
I'm not sure what all you might have so far, but to resolve that error perhaps try this instead:
expression: todecimal(fv!item.c11) - todecimal(fv!item.c3)
Hi Please understand my situation i have two date Start Date and End Date
for start date data:index(local!dataset.data,"c3",{}) and End date data:index(local!dataset.data,"c11",{}) in grid
i need to subtract these two date ( end date - start date ) to get the duration in days
EX- Jan 6, 2019 11:34 AM start date and end date is Feb 5, 2019 11:34 AM
Actually I think you might need it like this then:
a!gridTextColumn( label: "Duration", data: a!forEach( items: local!dataset.data, expression: todecimal(todatetime(fv!item.c11) - todatetime(fv!item.c3)) ) )
FYI, danb0002 - you can also do a lot of this troubleshooting yourself in a blank Expression Rule editor window, testing out what operators work with which date/datetime values, etc. That's what a lot of us do ourselves when providing support.
For ex.
with( local!today: datetime(2019, 12, 19, 15, 47), local!pastDate: datetime(2019, 12, 14, 07, 33), todecimal(local!today - local!pastDate) )
in some case start date is null
You would be able to handle this case in the logic of your a!forEach() expression once you get the math working for non-null cases. Just wrap the expression in an if() statement where you check i.e. "isnull(fv!item.c3)" and return a different value in that case.