Calculate days paid too late

[Anonymous]

Hello everyone, I have a question regarding time calculations

I have invoice data with Issue date & Last payment date. Time clients get to pay is standard 1 month. Here is some sample data:

The columns "Paid?" // "OnTime?" // "DaysLate" are calculated columns, the last one is the one I am having problems with.

Paid? = IF(FactHarvest_Invoices[Last Payment Date]<FactHarvest_Invoices[Issue Date],"NO","YES")

OnTime? = IF(FactHarvest_Invoices[Last Payment Date]>DATE(YEAR(FactHarvest_Invoices[Issue Date]),MONTH(FactHarvest_Invoices[Issue Date])+1,,DAY(FactHarvest_Invoices[Issue Date])),"YES","NO")

Now what I would like to do is in the DaysLate column is see the numbers of days that clients paid too late so 

DaysLate = FactHarvest_Invoices[Last Payment Date] - FactHarvest_Invoices[Last Payment Date] - 1 month
The problem is that I than get a date data type whiel I want an actuel number

Any ideas?

[amitchandak]

DaysLate = datediff(FactHarvest_Invoices[Last Payment Date] , FactHarvest_Invoices[Last Payment Date] , Day)-1

or

DaysLate = datediff(FactHarvest_Invoices[Last Payment Date] , FactHarvest_Invoices[Last Payment Date] , Month)

or

DaysLate = datediff(FactHarvest_Invoices[Last Payment Date] , FactHarvest_Invoices[Last Payment Date] , Day)

[edhans · Accepted solution]

Use the DATEDIFF() function to return an integer.

[Anonymous]

Hi @edhans that seemed to work, any idea how can substract the 1 month payment time tho?

Last Payment date - Issue Date = just the difference between the dates. But I want to know how many days have passed the 1 month payment date, if that makes any sense?

[edhans]

Well, how would you do it in Excel? For example, if the first date is Jan 1 and the 2nd is Mar 15, that is 74 days. How would you add/subtract a month from that? Assume 30 days? You cannot really add a month to an integer as month have 28-31 days. For example, I've always worked with days when dealing with AR agings, not months.

[Anonymous]

I fixed it like this!

DaysLate = DATEDIFF(FactHarvest_Invoices[Last Payment Date], DATE(YEAR(FactHarvest_Invoices[Issue Date]),MONTH(FactHarvest_Invoices[Issue Date])+1,,DAY(FactHarvest_Invoices[Issue Date])) , DAY)

[edhans]

Excellent @Anonymous - glad you figured out a solution that works!