cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Need solution to calculate Days Late in Excel Query Editor

I have a data source where I need to add a new column to display a calculated value of how many days late an action is past the due date, but if not late, then to display nothing.  Any ideas?

Example of existing column:

 

Contract Due Date

11/30/2019

3/30/2019

11/30/2018

1/19/2019

1/19/2018

 

I need to add a column that would show:

 

Days Late

0 or null

0 or null

19 (calculated as Today (12/19) minus 11/30)

0 or null

334 (calculated as Today (12/19 minus 1/19)

 

 

1 REPLY 1
Highlighted
Super User
Super User

Re: Need solution to calculate Days Late in Excel Query Editor

something like this

Days Late = MAX(TODAY()-Table[Contract Due Date],0)

if it's not overdue the TODAY() - Table[Contract Due Date] will return a negative number, which is always less than 0, If it will be overdue the number will be positive returning the correct value