Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

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')

4 Replies
campbellr
Creator

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')

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
vishsaggi
Champion III

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
Not applicable
Author

Hi Everyone,

Following did the trick. Thanks everyone for the help.

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