Calculating workdays between two dates for a farmers market that is open 4 days a week. This dueling podcast shows how to use a new feature introduced in Excel 2010 to calculate dates for any non-standard work week.
Transcript of the video:
Bill: Hey. Welcome back. It’s time for another Dueling Excel podcast. I'm Bill Jelen from MrExcel. I will be joined by Mike Girvin from ExcelIsFun. This is our episode 138, workdays for a farmers market, or any non-standard work week.
Alright. Tough question sent in today. We have a farmers market that's open on Tuesday, Wednesday, Thursday, and Friday.
Here's the start date. Here's the end date.
We want to calculate the number of workdays and the list of holidays.
Now, wow, it's been around forever. I remember back in Excel 2003, NETWORKDAYS, you had to have the analysis toolpack turned on. It would count all of the days that weren't Saturdays and Sundays. Then, in Excel 2007, they added network days international, and the beautiful thing about network days international is they allows you to specify alternate weekends right here. Here's what they gave us, the typical one, that is network days Saturday and Sunday, but any two consecutive days could be considered weekdays, and then for a 6-day work week, any particular one day, but you see none of these options here actually handle our situation where we're closed for 3 days, but here's the amazing thing. That tooltip did not change but, starting in Excel 2010, per Excel help, look…see, it still makes sense to read Excel help once in a while, you can now pass a new argument. This argument has to be 7 characters and it's comprised of 0s and 1s. 1 means they're closed on this day, 0 means they are open this day, and the order of the digits is Monday is the first digit, so, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday.
So, for example, in this case, I just kind of randomly typed some stuff here. They are open on Monday, closed on Tuesday, open on Wednesday, closed on Thursday, open on Friday, closed Saturday, open on Sunday, alright?
So, for our particular period here, they're open Tuesday, Wednesday, Thursday, Friday.
So, that means they're closed on Monday, and then 4 0s, Tuesday, Wednesday, Thursday, Friday, they're open, closed on Saturday and Sunday.
So, here's how we would set it up.
=NETWORKDAYS INTERNATIONAL, earlier date.
,. Later date ,. At the weekend, we just ignore the tooltip, and, in “, we’re going to type 1. That means closed on Monday, and then open on Tuesday, Wednesday, Thursday, Friday, closed on Saturday, Sunday. Close the “ , and then the holidays works just as normal in the function that's been there forever. [ =NETWORKDAYS.INTL(B7,B8,“1000011”,D7:D12) ] Now, this new feature started in Excel 2010.
So, it would work in Excel 2010, on the Mac in Excel 2011, or Windows Excel 2013. It will not work in Excel 2007. They won't know what to do with that. It's a cool feature. It works for any combinations of 1s and 0s, I think, except for if you are closed every day. So, you can't pass 7 1s. 1, 2, 3, 4, 5, 6, 7.
That will return an error. Otherwise, it's a great function. It works for, you know, any combination. Our local farmers market here is open Monday, Thursday, Friday, Saturday, so we would use that string. If you want to just count all the Tuesdays, send a whole big string of 1s but a 0 for the Tuesday position.
Great little function. Alright. Mike, let’s see what you have.
Mike: Thanks, MrExcel. That is just amazing.
I had no idea that you could do this in the weekend argument. 1 means don't count it, 0 means count. I guess it does pay to read help. If I click on this hyperlink in the screen tip and go to help, workdays international, if I scroll down, there it is, explaining exactly that this new feature is there, and what's so amazing is there's also two crazy array formulas we used to have to do for this or Barry Houdini date formulas.
With this new ability, wow, we have so many things we can do.
Now, I don't have a better formula for this.
This is just flat-out beautiful, but, hey, there's a bunch of things we can do. Let's look at conditional formatting. So, say we have this column down here and we just want to highlight the workdays. Now, normally, conditional formatting, you highlight the range, open up the conditional formatting dialog box, build your logical true/false formula, but let's build our formula first in the cell, copy it down to see how it works, and then we'll copy it and paste it into that dialog box.
NETWORKDAYS.INTERNATIONAL. Now, the trick is, for conditional formatting, either weekdays or weekends, start date , end date are the same. That means when we’re counting, we either get 1, yes this is a workday, or 0, it's not a work day. It's a weekend , and then there's our weekend. “ 1 means Monday is not included. Tuesday, Wednesday, Thursday, Friday, all 0s. That means we're counting them. Saturday, Sunday get 1s. We don't want to count those. That is amazing, , and then we have our weekend…or our holidays if you have holidays. F4, ). [ =NETWORKDAYS.INTL(A16,A16,“1000011”,$D$6:$D$11) ] Alright. I'm going to actually copy this in edit mode, CONTROL+C, and then let's CONTROL+ENTER and copy it down, and see what we get. No problem. Conditional formatting will interpret 1 as true, 0 as false. So, now we highlight.
Now, the keyboard shortcut…or let’s see.
Conditional formatting. HOME. CONDITIONAL FORMATTING. NEW RULE because we want to make a formula, a logical formula, true, false.
ALT+O D is the old keyboard shortcut. The new keyboard shortcut is ALT+H L N. Arrow, arrow, arrow. We use the USE FORMULA TO DETERMINE WHICH CELLS TO FORMAT, and then FORMAT VALUES WHERE THE FORMULA IS TRUE, CONTROL+V, and then you put whatever format in you want, and there we go. That is amazing.
Now, if this is for the actual days we're working, what if you wanted the weekends?
Well, guess what? I’m going to ALT+O…actually, let me build it off to the side. If I did CONTORL+V this, it would give me the weekdays.
I'm just going to wrap the NOT function around it. NOT is a weird function because all it does is convert true to false and false to true, so that'll be perfect. That'll work all the way down. Now, we’ll deliver a true or false which of course will work in conditional formatting. If we didn't want to do that, you know, the advantage to this is it somehow in…retains the integrity of that text there.
Maybe you wanted that, but no problem. It would be just as easy. Instead of putting a 1 for I don't want to count it, the Monday, that's the day we have off, now I'm going to put a 0, and then Tuesday, Wednesday, Thursday, Friday we don't want in this conditional formatting.
00 means Saturday and Sunday should be included, and then that will give us the opposite. Either one you like. [ =NOT(NETWORKDAYS.INTL(A16,A16,“1000011”,$D$6:$D$11)) ], [ =NETWORKDAYS.INTL(A16,A16,“0111100”,$D$6:$D$11) ] I'm going to try this one right here. Either one will work. CONTROL+C. Highlight. ALT+H L N, arrow, arrow, arrow, tab, CONTROL+V, and then some sort of formatting. Alright.
I'll throw it back to MrExcel.
Bill: Reading Excel help, it's funny. You know, I wrote my books during the beta. The help files aren't there during the beta and the little dropdown that appeared for the weekend of course doesn't mention this new thing. So, there's a feature that slipped by me and apparently slipped by a lot of people.
It's been in there for a while. So, a good one. Need to make a note to go back and read help on every function when the help files finally come out, just to see if they slipped something cool like that in there.
Alright. Well, hey. I want to thank everyone for stopping by. We'll see you next week for another Dueling Excel podcast from MrExcel and ExcelIsFun.
Alright. Tough question sent in today. We have a farmers market that's open on Tuesday, Wednesday, Thursday, and Friday.
Here's the start date. Here's the end date.
We want to calculate the number of workdays and the list of holidays.
Now, wow, it's been around forever. I remember back in Excel 2003, NETWORKDAYS, you had to have the analysis toolpack turned on. It would count all of the days that weren't Saturdays and Sundays. Then, in Excel 2007, they added network days international, and the beautiful thing about network days international is they allows you to specify alternate weekends right here. Here's what they gave us, the typical one, that is network days Saturday and Sunday, but any two consecutive days could be considered weekdays, and then for a 6-day work week, any particular one day, but you see none of these options here actually handle our situation where we're closed for 3 days, but here's the amazing thing. That tooltip did not change but, starting in Excel 2010, per Excel help, look…see, it still makes sense to read Excel help once in a while, you can now pass a new argument. This argument has to be 7 characters and it's comprised of 0s and 1s. 1 means they're closed on this day, 0 means they are open this day, and the order of the digits is Monday is the first digit, so, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday.
So, for example, in this case, I just kind of randomly typed some stuff here. They are open on Monday, closed on Tuesday, open on Wednesday, closed on Thursday, open on Friday, closed Saturday, open on Sunday, alright?
So, for our particular period here, they're open Tuesday, Wednesday, Thursday, Friday.
So, that means they're closed on Monday, and then 4 0s, Tuesday, Wednesday, Thursday, Friday, they're open, closed on Saturday and Sunday.
So, here's how we would set it up.
=NETWORKDAYS INTERNATIONAL, earlier date.
,. Later date ,. At the weekend, we just ignore the tooltip, and, in “, we’re going to type 1. That means closed on Monday, and then open on Tuesday, Wednesday, Thursday, Friday, closed on Saturday, Sunday. Close the “ , and then the holidays works just as normal in the function that's been there forever. [ =NETWORKDAYS.INTL(B7,B8,“1000011”,D7:D12) ] Now, this new feature started in Excel 2010.
So, it would work in Excel 2010, on the Mac in Excel 2011, or Windows Excel 2013. It will not work in Excel 2007. They won't know what to do with that. It's a cool feature. It works for any combinations of 1s and 0s, I think, except for if you are closed every day. So, you can't pass 7 1s. 1, 2, 3, 4, 5, 6, 7.
That will return an error. Otherwise, it's a great function. It works for, you know, any combination. Our local farmers market here is open Monday, Thursday, Friday, Saturday, so we would use that string. If you want to just count all the Tuesdays, send a whole big string of 1s but a 0 for the Tuesday position.
Great little function. Alright. Mike, let’s see what you have.
Mike: Thanks, MrExcel. That is just amazing.
I had no idea that you could do this in the weekend argument. 1 means don't count it, 0 means count. I guess it does pay to read help. If I click on this hyperlink in the screen tip and go to help, workdays international, if I scroll down, there it is, explaining exactly that this new feature is there, and what's so amazing is there's also two crazy array formulas we used to have to do for this or Barry Houdini date formulas.
With this new ability, wow, we have so many things we can do.
Now, I don't have a better formula for this.
This is just flat-out beautiful, but, hey, there's a bunch of things we can do. Let's look at conditional formatting. So, say we have this column down here and we just want to highlight the workdays. Now, normally, conditional formatting, you highlight the range, open up the conditional formatting dialog box, build your logical true/false formula, but let's build our formula first in the cell, copy it down to see how it works, and then we'll copy it and paste it into that dialog box.
NETWORKDAYS.INTERNATIONAL. Now, the trick is, for conditional formatting, either weekdays or weekends, start date , end date are the same. That means when we’re counting, we either get 1, yes this is a workday, or 0, it's not a work day. It's a weekend , and then there's our weekend. “ 1 means Monday is not included. Tuesday, Wednesday, Thursday, Friday, all 0s. That means we're counting them. Saturday, Sunday get 1s. We don't want to count those. That is amazing, , and then we have our weekend…or our holidays if you have holidays. F4, ). [ =NETWORKDAYS.INTL(A16,A16,“1000011”,$D$6:$D$11) ] Alright. I'm going to actually copy this in edit mode, CONTROL+C, and then let's CONTROL+ENTER and copy it down, and see what we get. No problem. Conditional formatting will interpret 1 as true, 0 as false. So, now we highlight.
Now, the keyboard shortcut…or let’s see.
Conditional formatting. HOME. CONDITIONAL FORMATTING. NEW RULE because we want to make a formula, a logical formula, true, false.
ALT+O D is the old keyboard shortcut. The new keyboard shortcut is ALT+H L N. Arrow, arrow, arrow. We use the USE FORMULA TO DETERMINE WHICH CELLS TO FORMAT, and then FORMAT VALUES WHERE THE FORMULA IS TRUE, CONTROL+V, and then you put whatever format in you want, and there we go. That is amazing.
Now, if this is for the actual days we're working, what if you wanted the weekends?
Well, guess what? I’m going to ALT+O…actually, let me build it off to the side. If I did CONTORL+V this, it would give me the weekdays.
I'm just going to wrap the NOT function around it. NOT is a weird function because all it does is convert true to false and false to true, so that'll be perfect. That'll work all the way down. Now, we’ll deliver a true or false which of course will work in conditional formatting. If we didn't want to do that, you know, the advantage to this is it somehow in…retains the integrity of that text there.
Maybe you wanted that, but no problem. It would be just as easy. Instead of putting a 1 for I don't want to count it, the Monday, that's the day we have off, now I'm going to put a 0, and then Tuesday, Wednesday, Thursday, Friday we don't want in this conditional formatting.
00 means Saturday and Sunday should be included, and then that will give us the opposite. Either one you like. [ =NOT(NETWORKDAYS.INTL(A16,A16,“1000011”,$D$6:$D$11)) ], [ =NETWORKDAYS.INTL(A16,A16,“0111100”,$D$6:$D$11) ] I'm going to try this one right here. Either one will work. CONTROL+C. Highlight. ALT+H L N, arrow, arrow, arrow, tab, CONTROL+V, and then some sort of formatting. Alright.
I'll throw it back to MrExcel.
Bill: Reading Excel help, it's funny. You know, I wrote my books during the beta. The help files aren't there during the beta and the little dropdown that appeared for the weekend of course doesn't mention this new thing. So, there's a feature that slipped by me and apparently slipped by a lot of people.
It's been in there for a while. So, a good one. Need to make a note to go back and read help on every function when the help files finally come out, just to see if they slipped something cool like that in there.
Alright. Well, hey. I want to thank everyone for stopping by. We'll see you next week for another Dueling Excel podcast from MrExcel and ExcelIsFun.
