Sunnee asks if there is a formula for identifying people who were tardy more than three times within an eight week period. Provided the data is sorted from earliest to latest, the formula in today's episode will solve the problem.
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn excel from MrExcel podcast. Episode # 1870 Tardy > three times within 8 weeks?
Hey welcome back to the MrExcel netcast. I'm Bill Jelen .
Today's question from YouTube has a list of dates and employees when they were tardy.
Wants to know if you can set up a formula to calculate if someone was tardy more than 3 times within an 8 week period and yes, we can do that.
I'm gonna set up the formula starting down here.
Just because it's easier to think about when you have a chunk of data, and this is also actually Emily's second tardy here.
So we're going to use a function called COUNTIFS COUNTIFS and they want a criteria range 1 and the Criteria so first we are going to look through all of the employees above us including this row and see if it's equal to Emily and then the second criteria is going to be looking at all of the dates above us including this row and see if it is greater than so in quotes, I'm going to put it greater than and then concatenate that with in parentheses this date - 56556 because there's 56 days within 8 weeks including Saturdays and Sundays.
Even if you don't work Saturdays and Sundays, those have to be factored in. All right so that finishes up the criteria range another parenthesis to close the COUNTIFS and sure enough Emily has been tardy 2 times within the last 8 week period. Ah but before I can copy this formula, I have to go through and very carefully put some dollar signs in.
I always want to start in row 4.
So I'll put I'll press F4 there to put dollar signs there that was F4 twice.
But the B12 has to be allowed to range down to the current row so no dollar signs there or there, but here this a A4 I'll just type a single dollar sign.
We always want to go back up to row 4 that way those criteria range start at row 4 and go down to the current row.
Now I added this note over here, so I wouldn't forget to tell you for this techniques to work, I'm assuming that your data is sorted in an ascending order earliest to latest.
That's the assumption for this formula to work and if it's not simple enough to sort the data.
It can even be sorted by date within employee. That's fine.
But you have to make sure that the dates aren't just in there randomly.
Your most recent first or anything like that and so there's our formula. Will double click to copy this down and also copy and paste up.
Ctrl + V Alright its now keeping track of how many times people have been tardy within the last 8 weeks.
So here Emily is tardy 3 times.
We're looking for people who are tardy more than 3 times.
So how would I do this?
I want to find all of these. We can turn on the filter and look for everything that is not 1 2 or 3.
Click OK and there are all of the incidences of people that have been tardy.
It's just interesting let's choose Jayden here and make sure that at some point the numbers go back down, so it keeps increasing until we get out to 56 days, and then see there's actually a few that fall off because they are earlier than the period so it looks like its working.
Interesting question provided the data is sorted with dates ascending that formula should work.
Hey. I want to thank your stopping by.
Will see you next time for another netcast from MrExcel.
Learn excel from MrExcel podcast. Episode # 1870 Tardy > three times within 8 weeks?
Hey welcome back to the MrExcel netcast. I'm Bill Jelen .
Today's question from YouTube has a list of dates and employees when they were tardy.
Wants to know if you can set up a formula to calculate if someone was tardy more than 3 times within an 8 week period and yes, we can do that.
I'm gonna set up the formula starting down here.
Just because it's easier to think about when you have a chunk of data, and this is also actually Emily's second tardy here.
So we're going to use a function called COUNTIFS COUNTIFS and they want a criteria range 1 and the Criteria so first we are going to look through all of the employees above us including this row and see if it's equal to Emily and then the second criteria is going to be looking at all of the dates above us including this row and see if it is greater than so in quotes, I'm going to put it greater than and then concatenate that with in parentheses this date - 56556 because there's 56 days within 8 weeks including Saturdays and Sundays.
Even if you don't work Saturdays and Sundays, those have to be factored in. All right so that finishes up the criteria range another parenthesis to close the COUNTIFS and sure enough Emily has been tardy 2 times within the last 8 week period. Ah but before I can copy this formula, I have to go through and very carefully put some dollar signs in.
I always want to start in row 4.
So I'll put I'll press F4 there to put dollar signs there that was F4 twice.
But the B12 has to be allowed to range down to the current row so no dollar signs there or there, but here this a A4 I'll just type a single dollar sign.
We always want to go back up to row 4 that way those criteria range start at row 4 and go down to the current row.
Now I added this note over here, so I wouldn't forget to tell you for this techniques to work, I'm assuming that your data is sorted in an ascending order earliest to latest.
That's the assumption for this formula to work and if it's not simple enough to sort the data.
It can even be sorted by date within employee. That's fine.
But you have to make sure that the dates aren't just in there randomly.
Your most recent first or anything like that and so there's our formula. Will double click to copy this down and also copy and paste up.
Ctrl + V Alright its now keeping track of how many times people have been tardy within the last 8 weeks.
So here Emily is tardy 3 times.
We're looking for people who are tardy more than 3 times.
So how would I do this?
I want to find all of these. We can turn on the filter and look for everything that is not 1 2 or 3.
Click OK and there are all of the incidences of people that have been tardy.
It's just interesting let's choose Jayden here and make sure that at some point the numbers go back down, so it keeps increasing until we get out to 56 days, and then see there's actually a few that fall off because they are earlier than the period so it looks like its working.
Interesting question provided the data is sorted with dates ascending that formula should work.
Hey. I want to thank your stopping by.
Will see you next time for another netcast from MrExcel.
