Column Formula to count (and sum) 'Waiting Days'

Jim B

Hello,

Can anyone suggest a way to manage this?

I'd like to be able to count up time spent awaiting feedback from our client across various projects held in the same sheet. Essentially I need to be able to count the days between when a Version is sent and when Feedback is received. If Feedback is received on the same day that a Version is sent then I would like to count that as a half day waiting.

The milestones recorded in the 'Event' column are powered by a drop down, ie the labelling/naming conventions are consistent. Essentially I need a formula to count elapsed days between each 'phase' of Version Sent and Feedback Received. Other events can occur around and sometimes within these phases which obviously would need to be ignored for the formula to work consistently.

Once I have days elapsed in phases in one column then presumably I would need another column to sum within each project. I think I can manage that one.

Here's an example of how the data looks:

Thanks in advance.

Genevieve P.

Hi @Jim B

You could use a nested IF formula to check for your criteria. First, we'll want to return Blank if the current row is not a "Feedback Received" row:

=IF(Event@row <> "Feedback Received", "",

But if it is this type of row, we'll want to check to see if the row above is "Version Sent" (ex. if the formula is in row 3, we check the cell in row 2):

IF(Event2 = "Version Sent",

AND then if the End date of the current row is the same as that End Date, we want it to = 0.5

IF([End Date]@row = [End Date]2, 0.5,

otherwise, find the number of days between the two dates:

[End Date]@row - [End Date]2),

This means so far we have:

=IF(Event@row <> "Feedback Received", "", IF(Event2 = "Version Sent", IF([End Date]@row = [End Date]2, 0.5, [End Date]@row - [End Date]2)

Which would be fine as long as your two rows would always be next to each other. However if there's a possibility that there's another event between the Sent and Received, we'll need to add another IF statement to check 2 rows above:

IF(Event1 = "Version Sent", IF([End Date]@row = [End Date]1, 0.5, [End Date]@row - [End Date]1))

Now you have a formula that should check the row above, and if that's not "Version Sent", it will go a row above that:

=IF(Event@row <> "Feedback Received", "", IF(Event2 = "Version Sent", IF([End Date]@row = [End Date]2, 0.5, [End Date]@row - [End Date]2), IF(Event1 = "Version Sent", IF([End Date]@row = [End Date]1, 0.5, [End Date]@row - [End Date]1))))

Are there possibilities that you could have more than 1 row in between your two Events? If so, you'll need to add another IF statement at the end, checking the 3rd row above, then the 4th, etc.

Keep in mind that since we're using row numbers to check the rows above, you'll need to drag-fill the formula down the column in order to populate it in each row.

Let me know if this makes sense and works for you!

Cheers,

Genevieve

Genevieve P.

Hi @Jim B

You could use a nested IF formula to check for your criteria. First, we'll want to return Blank if the current row is not a "Feedback Received" row:

=IF(Event@row <> "Feedback Received", "",

But if it is this type of row, we'll want to check to see if the row above is "Version Sent" (ex. if the formula is in row 3, we check the cell in row 2):

IF(Event2 = "Version Sent",

AND then if the End date of the current row is the same as that End Date, we want it to = 0.5

IF([End Date]@row = [End Date]2, 0.5,

otherwise, find the number of days between the two dates:

[End Date]@row - [End Date]2),

This means so far we have:

=IF(Event@row <> "Feedback Received", "", IF(Event2 = "Version Sent", IF([End Date]@row = [End Date]2, 0.5, [End Date]@row - [End Date]2)

Which would be fine as long as your two rows would always be next to each other. However if there's a possibility that there's another event between the Sent and Received, we'll need to add another IF statement to check 2 rows above:

IF(Event1 = "Version Sent", IF([End Date]@row = [End Date]1, 0.5, [End Date]@row - [End Date]1))

Now you have a formula that should check the row above, and if that's not "Version Sent", it will go a row above that:

=IF(Event@row <> "Feedback Received", "", IF(Event2 = "Version Sent", IF([End Date]@row = [End Date]2, 0.5, [End Date]@row - [End Date]2), IF(Event1 = "Version Sent", IF([End Date]@row = [End Date]1, 0.5, [End Date]@row - [End Date]1))))

Are there possibilities that you could have more than 1 row in between your two Events? If so, you'll need to add another IF statement at the end, checking the 3rd row above, then the 4th, etc.

Keep in mind that since we're using row numbers to check the rows above, you'll need to drag-fill the formula down the column in order to populate it in each row.

Let me know if this makes sense and works for you!

Cheers,

Genevieve

Jim B

Thanks, Genevieve.

This is really helpful. Thank you.

Jim