Delays Log - Calculate the total number of working days each task is delayed

[alexkoustas]

Hi everyone,
I have a client for which there are multiple tasks to complete. For each task, I may raise numerous requests for more information (via email usually).
I would like to calculate the total number of working days each task is delayed as a whole, as a result of waiting to receive the information I need from the client.

Please can you propose a formula which would be suitable to be in the cell I have highlighted in red in the attached example?

Please consider that:

1. The formula must not double count the working days for requests that are happening at the same time/overlapping.
2. The requests may be more than 4.
3. I'm expecting to have at least 60-70 tasks (the example in the attachment only has 5).
4. The number of requests may end up to be a few hundreds.

I really appreciate any help you can provide.
Alex.

[vba_php]

given your 4 points, I would guess no Iface formula exists. others here would know if it does though. I wouldn't. only thing I can think of is to loop the col you want to target and use VBA's date function pool to analyze, capture and add days to the total. see attached archive. it contains code I wrote long ago to calculate number of working days in a single col in an excel sheet and return it in a variable. it can be modified to suit your needs. one txt has the original code. the other contains the stripped down equivalent for excel. that one loops one col.

Please Login or Register  to view this content.

[alexkoustas]

Thank you Adam, much appreciated.
This function seems to be relevant but I suck a bit at VBA. I will give it a go though.

Hopefully somebody will come up with a formula to help out a bit more.

[vba_php]

I actually messed it up. whops! it should be this:

Please Login or Register  to view this content.

sorry about that! give it a shot and get back to us. that way you feel good about urself.