• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Consecutive Days Analysis in Excel

Hello,

I would appreciate your help with the following:

Assuming I have a table with ID #, date and hours per date as follows:
I would like to know if there were any employees who recorded 9 hours for five consecutive days pr or more (business days i.e. Monday to Friday) - it must be business days.

1) so if a employee charged 3 days 9 and 1 day 2 and the next day 9 - this is no good

2) only consecutive days of 9 hours per days for business (so Wednesday, Thursday, Friday and Monday and Tuesday - -great

-----> output
I would like to create a table which would present per ID #, the maximum count of consecutive days with 9 hours in each day. If there is a weekend in between than the count should skip the weekend. [see excel for the example]

I hope that someone can help me with the problem. I am totally stumped, and very very key to exclude Saturday and Sunday form the analysis.

I have attached an excel with the sample data and of course example of the desired results. I have added scenarios that should work and those that should not, based on the criteria above.

Thank you to anyone who can solve this for me, and provide me with the excel logic to this, - please updated the table to show how

Many thanks once again. I hope my explanation was clear. I have googled and tried - but with little or no success.

Now over to the experts :)
 

Attachments

  • Consecutive Days - Business Days.xlsx
    15.2 KB · Views: 35
@david gabra

Interesting question. You can use simple formulas to find the streak of 9 hour workdays. You can then apply filters on streak >= 5 to see desired items.

For example, you can use below formula to calculate the streak
Assuming ID# in A2, Date in B2, Hours in C2, and streak goes in column D

=IF(A2<>A1,1, IF(OR(NETWORKDAYS(B1,B2)<>2,MIN(C1:C2)<9),1,SUM(D1,1)))

We just check if there is a new ID (A2 <> A1) and if so, we check if the dates are consecutive workdays with NETWORKDAYS and hours are more than 9. If all conditions are met, then we increase the streak by 1, else we reset it to 1.

You can set up another column to check if streak values in D are maximum values for corresponding ID#, by using MAX(IF(..)) array formula like below.

=MAX(IF($A$2:$A$25=A2,$D$2:$D$25))=D2

This way, you can just fitter by max streaks.

While this is not same as the output you want, once you have this format, you can easily turn it in to output with INDEX or VBA.

All the best.
 
I really really have no idea

For me it is not easy :-(

While this is not same as the output you want, once you have this format, you can easily turn it in to output with INDEX or VBA
 
Hi
this is genius
=IF(A2<>A1,1, IF(OR(NETWORKDAYS(B1,B2)<>2,MIN(C1:C2)<9),1,SUM(D1,1)))
wow
- this should be good enough :)

just to let you know - the
=MAX(IF($A$2:$A$25=A2,$D$2:$D$25))=D2
since if filter by true - does not give the result, look at emp 444-
- its a nice to have, if you cam ammend
but thank you so so so so so much!!!!!!
 
See attached.
Array-Enter the formula in a range 3 cells wide by 1 row deep. Then copy down.
upload_2017-8-14_20-54-10.png
 

Attachments

  • Consecutive Days - Business Days.xlsm
    22.3 KB · Views: 59
Back
Top