How to eliminate the negatives as a result of date difference calculation between [INTran.ReleasedDateTime] and Today()

Hi ​@lorach,
 

You may want to reverse the DateDiff parameters. Try using:
=DateDiff('d', [INTran.ReleasedDateTime], Today())

If you need to ensure positive values regardless of date order, you can wrap it with Abs().

Hi ​@lorach  Could you please try this 

DateDiff('d', [INTran.ReleasedDateTime], Today())

Hi ​@abhimanyuprajapati52 and ​@Meri36 , I forgot to include that in, but I had tried it and it was still giving me the wrong values 

​@lorach Have you tried this option?

=DateDiff( 'd', Date([INTran.ReleasedDateTime]), Date(Today()))

​@Meri36 I did but unfortunately the formula operator ‘Date’ is unsupported

​@lorach, 
you can try
=Abs(DateDiff('d', [INTran.ReleasedDateTime], Now()))

​@lorach  I have tried with this formula 
DateDiff('d', [INTran.ReleasedDateTime], Today())

and today is 02/05/2026 on my system,
difference is 240 days 

could you please show the result when you run this formula

Hi ​@abhimanyuprajapati52 and ​@Meri36 , I have both options and I get the same result, it’s interesting that ​@Meri36 is getting the right values 🤔

​@lorach could you please share the GI xml file?
I would like to try the same on my system

​@lorach 
 

Since the difference between 2/4/2026 and 2/5/2026 should be 1 day, the values 334 or 508 suggest the formula may be referencing a different INTran record than the one displayed.

Could you verify:
• Whether there are multiple joins causing row duplication
• That the ReleasedDateTime used in the formula matches the field displayed in the grid
• Or try adding ReleasedDateTime directly as a column in GI to confirm the exact value being evaluated

It seems more like a data context issue rather than a DateDiff calculation issue.

​@Meri36 ,I have attached the xml, just a heads up, in case you are wondering why so many table it’s because it’s a custom project allocated inventory GI which is listing the results correctly, I just needed to get the date difference btn the two dates I have mentioned earlier in order to determine some project specific deadstock.

I have also attached the original project allocated inventory report that I worked on before adding the new tables in order to get the [INTran.ReleasedDateTime] and something I forget to mentioned in the first report I shared, I  tried using [INTran.TranDate] as you will see in the result grid, but it got the same results.

​@lorach 

 

No problem at all for to many tables, 
but as I see the problem is related to the Grouping/joins you have in GI, not the formula, the formula itself is correct

​@Meri36 Ok, I guess I will have a thorough double checking of the Groupings and joins

​@lorach ,

I imported your GI XML into a clean instance and tested it without modifications. The DateDiff formula is returning correct results (e.g., 2/5/2026 to 6/28/2026 = 143 days). This suggests the issue may not be with DateDiff itself but possibly with which INTran record is being evaluated due to joins or grouping. You may want to temporarily expose DocType, RefNbr, and ReleasedDateTime in the grid to verify the exact INTran record used in the calculation.

​​​Hi ​@ricoybanez , Thank you so much, your formula actually fixed it 🤗👍