Need help with calculating number of days between today's date and last transaction

[Anonymous]

I am a newbie.

I am trying to get number of days between today's date and transaction date. Can someone please help me fix expression below.

=date((date('today's date')-date('[CustomerImportantDates.Last Invoiced Date]')),'DD')

[campbellr]

Hi Muhammad

The problem with using the date() function with 'DD' as a format is you're returning a date and showing the day proportion, so today() = 19 April 2018. if your last invoiced date was 1 March then the difference you're looking for is 49 but the resultant date of 49 is 17 as the date difference will then be converted to a date = 17th February and the 'DD' makes it show the 17. Instead you want to use num() to return the number of days between the dates.

The format of '#.' will force a whole number. using '#' may get displayed as scientific notation. Also if your invoice date is a datetime field you will get fractional portion to the result.

If you have a last invoiced date

Give this a try:

=num((today()-date('[CustomerImportantDates.Last Invoiced Date]')),'#.')

if you just have a date field you can refer to try:

=num(today()-max([Invoice Date]),'#.')

[Anil_Babu_Samineni]

Perhaps this?

=NetWorkDays(Date(Today()),Date([CustomerImportantDates.Last Invoiced Date]))

OR

=Interval(Date(Today())-Date([CustomerImportantDates.Last Invoiced Date]),'DD')

[vishsaggi]

Is your date format of lastInvoicedDate is same as Today() dateformat. If yes, you can just use

= Date(Today(), 'MM/DD/YYYY') - Date(LastInvoicedDate, 'MM/DD/YYYY')

OR

= Interval(Date(Today(), 'MM/DD/YYYY') - Date(LastInvoicedDate, 'MM/DD/YYYY'), 'DD')

[Anonymous]

Hi Everyone,

Following did the trick. Thanks everyone for the help.

=Num#(Date(Today())-Date([CustomerImportantDates.Last Invoiced Date]),'#.')