How can I calculate the total days of a column when another column meets a criterion?

Raquel Charaba

Hi everyone,

I need help creating a formula that collects the total days from the date a request is inputted to the day it changes from "Pending" to "Approved".

Dates are registered in the column "Date Initiated"

Status is registered in the column "Review"

I created a "Today's Date" column to subtract the requested date and an "Approval Waiting Time" to sum the net days between the two dates. However, I am struggling to create a formula in my summary that shows total days only when the status is Pending.

Can you help me with this, please?

Mike TV

@Raquel Charaba

Try this:

=VALUE(SUM([Approval Waiting Time]:[Approval Waiting Time]))

Mike TV

@Raquel Charaba

Instead of a column for Today's Date what you need is a column for Date Approved. Then an automation to record a date into that column when the status changes to Approved. Then you do NETDAYS between the Date Initiated and Date Approved.

Mike TV

@Raquel Charaba

Also, without a date in the Date Approved column you're going to get an error with the NETDAYS function. So you wrap that around in an IFERROR that just leaves the field blank when there's an error due to no date yet in the Date Approved column.

Raquel Charaba

Hi @Mike TV

Thank you for your help.

I settled up the automation and it worked however I am having an error in the NETDAYS column.

I checked and both columns' properties are dates. When I try to add dates manually the formula works. Do you how can I fix that?

Thank you!

Mike TV

@Raquel Charaba

The column your NETDAYS formula is in should be text/number. If it's a date column then that could be what is causing the problem. The other 2 columns the Date Approved and Date Initiated should be date columns.

Raquel Charaba

I was missing one of the dates in a column and the formula could not work due to that. Now it's working!

Thank you!

Raquel Charaba

Hi @Mike TV

The net days' column is working now but I can't make the sum formula in my Summary sheet work.

This is the formula I am using: =SUM([Approval Waiting Time]:[Approval Waiting Time])

However, it says #INVALID DATA TYPE. Do you know how can I fix that?

Mike TV

@Raquel Charaba

Try this:

=VALUE(SUM([Approval Waiting Time]:[Approval Waiting Time]))

Raquel Charaba

@Mike TV

Thank you for your help.

It still shows me #INVALID DATA TYPE. I think it's because some rows don't have any data and the formula shows as #INVALID DATA TYPE.

Mike TV

@Raquel Charaba

I believe you need to add to the formula in the Approval Waiting Time column so the errors go away from it.

You can add the IFERROR to the beginning of your formula and the result for an error would be double quotes to make it leave the cell blank if there'd be an error. After doing that, this other formula might clear up.

Raquel Charaba

@Mike TV

Thank you so much.

It works now :)