AVG of Dates

[rodri_morales]

How can I get the AVG between the day the bill come and the day we paid the bill?

I make this:  =num(avg(day(DatePaymentBill-DateReceptionBill)), '##,##')

But I have a problem, sometimes we paid before the bill come (like a prepayment) and other times we paid after the bill come. So I wanna make 2 text object with this average.

[CELAMBARASAN]

Try something like this

avg(Aggr(DatePaymentBill-DateReceptionBill, BillID))

[Gysbert_Wassenaar · Accepted solution]

Try these two

Pre-paid:

=num(avg(if(DatePaymentBill<DateReceptionBill, DateReceptionBill -DatePaymentBill)), '##,##')

Paid afterwards:

=num(avg(if(DatePaymentBill>=DateReceptionBill, DatePaymentBill-DateReceptionBill )), '##,##')

[rodri_morales]

I do that, but I think it isn't the correct value. AVG(DatePaymentBill-DayReceptionBill) returns me the average in days?

[rbecher]

What else you are expecting? There is no average date.. However, you could add the avg days to the DayReceptionBill to get the expected DatePaymentBill if this makes sense.

[rodri_morales]

I want to know if the difference between DatePaymentBill and DateReceptionBill is in days. If not, I have to add the condition day()

I have this:

Without Prepayment

=num(avg(if(DatePaymentBill>=DayReceptionBill, DatePaymentBill-DateReceptionBill)), '##,##')

With Prepayment

=num(avg(DatePaymentBill-DateReceptionBill), '##,##')

If I add the function day() to the avg, the values are incorrect, because Without Prepayment < With Prepayment and thi isn't correct. Always AVG with Prepayment must be less than AVG without Prepayment