Forum Discussion

IrisBonilla's avatar
IrisBonilla
Qrew Cadet
7 years ago

today's date minus input date

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?
Formulas and functions
  • LauraThacker's avatar
    LauraThacker
    Qrew Captain
    7 years ago
    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?
  • IrisBonilla's avatar
    IrisBonilla
    Qrew Cadet
    7 years ago
    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. 
  • LauraThacker's avatar
    LauraThacker
    Qrew Captain
    7 years ago
    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)