Date Formula Help

I need a formula that will count the day that a row is "Open" but stop counting the days once its "Closed" both instances need to display the count.

Here is a formula I was trying to use. Thanks

=IF([Date Closed]@row > 0, ([Date Closed]@row - [Date Opened]@row), TODAY() - [Date Opened]@row)

Best Answer

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    How about rather than using the logic for date closed being today or before today, we just use the fact there is a date closed.

    =IF([Date Closed]@row <> "", [Date Closed]@row - [Date Open]@row, TODAY() - [Date Open]@row)

    If the [Date Closed] is populated the calculation is [Date Closed]-[Date Open]

    If there is no [Date Closed] then the calculation is TODAY() - [Date Open]@row


    Would that work for you?

Answers

  • mkoch
    mkoch ✭✭✭✭
    edited 12/01/23

    Would this work for you?

    =IF([Date Closed]@row <= TODAY(), [Date Closed]@row - [Date Open]@row, TODAY() - [Date Open]@row)



  • LeoCicio
    LeoCicio ✭✭
    edited 12/01/23

    its not displaying the days if closed isnt filled in. I still need it to show the days if its not closed but stop counting if it is closed and still show the days. does that make sense?

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓

    How about rather than using the logic for date closed being today or before today, we just use the fact there is a date closed.

    =IF([Date Closed]@row <> "", [Date Closed]@row - [Date Open]@row, TODAY() - [Date Open]@row)

    If the [Date Closed] is populated the calculation is [Date Closed]-[Date Open]

    If there is no [Date Closed] then the calculation is TODAY() - [Date Open]@row


    Would that work for you?

  • KPH This looks correct as long as the days stop calculating once closed which looks like they should.

  • KPH
    KPH ✭✭✭✭✭✭

    Yes, If the [Date Closed] is populated the calculation is [Date Closed]-[Date Open]