There are a couple of obscure functions in the analysis toolpak that will allow you to calculate how many workdays have elapsed. Enter a hire date and today's date, and you can calculate the number of workdays. This function even allows for you to enter a range of company holidays which are factored into the calculation. Episode 399 shows you how.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Today we have a question that came in in one of my power Excel seminars that was in the last couple of weeks.
They wanted to try and figure out how many workdays someone had worked.
So we have a Hire Date, and we know what today is, we want to try and figure out if that person has worked enough days to get out of their Probationary period.
And there's a couple of great functions available, but they're only in the Analysis tool-pack.
So before you try these functions, you need to go to Tools, Add-Ins, and make sure that the Analysis tool-pack option is checked.
It's always installed, but it's not always turned on by default on every computer.
The other beautiful thing about this function is that it can handle your own company work days.
So you see, off to the right here, I've entered the days that we have off in 2007, and to create the formula we have the Hire Date in column B, today's date in column C. And then the function in column D is called NETWORKDAYS.
And you have to specify the first date, the last date, and then optionally, specify the range of holidays, and Excel will go through and count how many Monday through Fridays they worked minus the holidays.
So accept this, it works, and then over here in column E, I have a simple IF function, if the result in D is greater than 60 use Regular, otherwise Probationary.
The other function that’s related to NETWORKDAYS is called the WORKDAY function, this is if you need to figure out when the probationary period ends.
So we start from the date in cell B4, extend out 60 workdays, keeping in mind the holidays in this range off to the side, and that will calculate a new date for you.
So two great functions, the WORKDAY function and the NETWORKDAYS, plural, function, will allow you to figure out, if your work week is Monday through Friday, any number of work days in the future, or how many work days have elapsed.
Hey, there you have it, stop by next time for another tip from MrExcel!
Today we have a question that came in in one of my power Excel seminars that was in the last couple of weeks.
They wanted to try and figure out how many workdays someone had worked.
So we have a Hire Date, and we know what today is, we want to try and figure out if that person has worked enough days to get out of their Probationary period.
And there's a couple of great functions available, but they're only in the Analysis tool-pack.
So before you try these functions, you need to go to Tools, Add-Ins, and make sure that the Analysis tool-pack option is checked.
It's always installed, but it's not always turned on by default on every computer.
The other beautiful thing about this function is that it can handle your own company work days.
So you see, off to the right here, I've entered the days that we have off in 2007, and to create the formula we have the Hire Date in column B, today's date in column C. And then the function in column D is called NETWORKDAYS.
And you have to specify the first date, the last date, and then optionally, specify the range of holidays, and Excel will go through and count how many Monday through Fridays they worked minus the holidays.
So accept this, it works, and then over here in column E, I have a simple IF function, if the result in D is greater than 60 use Regular, otherwise Probationary.
The other function that’s related to NETWORKDAYS is called the WORKDAY function, this is if you need to figure out when the probationary period ends.
So we start from the date in cell B4, extend out 60 workdays, keeping in mind the holidays in this range off to the side, and that will calculate a new date for you.
So two great functions, the WORKDAY function and the NETWORKDAYS, plural, function, will allow you to figure out, if your work week is Monday through Friday, any number of work days in the future, or how many work days have elapsed.
Hey, there you have it, stop by next time for another tip from MrExcel!
