today's date minus input date

  • 0
  • 1
  • Question
  • Updated 4 months ago
  • Answered
Hello! 

I'm trying to calculate the number of days past a due date. That means it would be the today's date minus the due date so it can show me how many days in total it has been past due. The due dates are in a column with a date type field.

I tried today()-duedate, but it doesn't seem to work. Any help?
Photo of Iris Bonilla

Iris Bonilla

  • 320 Points 250 badge 2x thumb

Posted 4 months ago

  • 0
  • 1
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 2,266 Points 2k badge 2x thumb
Is your formula field formula-numeric or formula-duration?

Today() - [Due Date]

Are there any errors indicated in the formula itself?  Can you provide a screenshot of the [Due Date] column and formula-field in a report showing where it is returning a bad result?
Photo of Iris Bonilla

Iris Bonilla

  • 320 Points 250 badge 2x thumb
Its numeric, but should I change is to formula duration? Because in reality it is number of days passed the original due date. For example, a project was due 12/22/17 (due date is a typical date field), so minus today's date 7/1/18, the number of passed days is 192. 
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 2,266 Points 2k badge 2x thumb
If you're using a numeric-formula field to generate the days, you will need to use

ToDays(Today()-[Date])

In your example, using todays date, I get 195 days.  Depending on whether your field is indicated to be "Past Due" or not; you may want to use [Date] - Today() instead.  In other words, if you use Today() - [Date] you will get a negative number.

If the intent is to indicate the number of days past due, then you may want to modify the formula so it does not output anything if the [Due Date] is still in the future.

If(Today()>[Date],
ToDays([Date]-[Today]),null)