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.
Hi Peter i understand that but I'm getting the result
0::00:00:11.207
which i don't need that i need this result only in days . because some task assigned in same date and completed in same date so for that scenario it should be 0 .... i hope you are getting my point
I suggest you write an expression rule called something like "APP_getElapsedDaysBetweenDates" that accepts two DATETIME parameters and returns a result that matches what you are after - i.e. whole numbers (0, 1, etc) for business days, or decimals to represent days and partial days, and also handles null-checking on either or both of the inputs. After that, you can simply call that rule and pass in the two columns from your report within the logic of your a!forEach statement. Doing it this way will make it much more easy to initially test that the math you're trying to do is both working without error, and returning the exact return value you're expecting.
Agreed with Mike - troubleshooting is much easier one at a time. Also, since you're looking for just the days, I'd suggest putting floor() around your interval result, which should give you the number of days in an integer.
Thanks Peter , I got my solution