Sanjeev needs a function like NETWORKHOURS() but there is no such function in Excel. Bill Jelen from MrExcel and Mike Girvin from ExcelisFun try different approaches for solving the problem.
Transcript of the video:
Hey, that's right it's time for another Dueling Excel podcast.
I am Bill Jelen from MrExcel. I will be joined by Mike Girvin from ExcelisFun.
This is our Episode #191.
NetWorkHours spanning midnight. Hey, welcome back to the Dueling Excel podcast.
This is a question that I'm sure that we've dealt with years ago.
But it would be interesting to see if there are new ways to do it given that we have LET() and Power Query. So, Mike the the question today.
This is from Sanjeev.
He has a start date and time and an end date and time.
He needs to figure out the number of work hours between the two.
The company that Sanjeev works for, their shifts they start on Monday at 5 pm and go to Tuesday at 4 am. This then repeats Tuesday, Wednesday, Thursday, Friday.
This is the thing that really really drives me crazy because that means that we need to check Monday from 5PM to midnight and then Tuesday from midnight to 4AM.
There's a whole bunch of things that we have to check for here and then a little part of Saturday that we have to work - - we have to include as work hours. Now the complication.
Sanjeev doesn't have Microsoft 365. He has Excel 2016. So no LET or Dynamic Arrays.
At least when I was solving it.
And then Sanjeev says, "My company says that three hours and 36 minutes should be reported as 3.36" Well, that's going to drive me crazy all right.
So first thing I realized that having shifts that span midnight might complicate everything because then instead of just five shifts you essentially have 10 shifts because you're breaking them apart.
My first step is: I am going to take all of Sanjeev's times and I am going to subtract four hours and one second from them all.
Then it becomes really simple.
I just have five shifts that I have to worry about from Monday at 3 p.m to midnight.
And then it's just Monday through Friday. So i am going to restate this Mike.
This is my simpler question.
We need a formula to figure out how many hours between 1500 and midnight on Monday through Friday.
NETWORKDAYS() is back in play because we don't have to worry about Saturdays and Sundays now.
Although, it would be really nice if we had a NETWORKHOURS function.
The rules for the duel: use anything in Microsoft 365 Insider's Beta so LET?
Absolutely! XLOOKUP? Go for it! Dynamic arrays? Yes.
and as far as reporting 3.36 to mean 3 hours and 36 minutes. It's just nonsense.
What I am going to do is: I am going to fix that with a custom number format of bracket h bracket dot MM so we're still going to be reporting this as time.
It's just going to look the way that Sanjeev's manager wants it to look. All right. So here's my start.
We have these different examples.
Sanjeev gave me a few of these to trip me up.
I added more to handle all the other things that could trip us up. My plan: Step one.
Back-date everything by four hours. Step 2.
Break out the day from the time using the INT() function. Step 3. Is it the same day?
That seems like a simpler case. So, I want to be able to handle that first.
And now, after the fact, I am wondering if this decision complicated things. If i should have just ignored this.
So I don't want this to cloud your judgment, Mike. Step 4.
Break out the time from the day using MOD(,1). Step 5. Handle the simple case of same day.
It is ending time minus starting time.
But if the starting time is before the work day starts you want to use the later of those two. So the max of those.
Step 6. Then if it's not the same day, how many whole work days are there?
Then multiply that by nine hours a day. Step 7. Then how many hours on the first day?
So, that's midnight minus start time but again start time or 1500 whichever is later. Step 8.
For the last day it's the end time minus 14.59 but it could never be negative.
I don't know why they would ever end it before the workday. I can imagine something where someone stays past four AM.
I don't know. Who knows. Step 9. Sum all the times.
Format with [ H ].MM. Alright.
So I will run through these formulas really quickly.
Take the original time minus four hours and one second. Take the original end time minus four hours and one second.
Simple enough. That's Step 1. Easy! Step 2.
Calculate the adjusted start to a date. So getting rid of the time.
Getting rid of the 14:59 and backing up to 0.00.
I take the INT() the integer function of both of those. Step 3. Is it the same day?
So is this F9 equal to G9?
If I was able to use LET this would be really handy, I think. Okay step 4.
Break out the time from the day. So right here we have the date portion.
Oh, you know now, why didn't I just subtract F9 from D9? That would have been easier.
But instead. I used the MOD function because it makes me look geekier, I guess.
The MOD(D9,1) gets rid of the whole days and gives us just the time. And the end time. Okay now the easy case.
To me it seems easy. If this is the same day.
If H9 is TRUE then we're going to take the end time minus the start time.
But we want to make sure that the end time is never greater than 23:59:59.
And we have to make sure that the start time is never earlier than 14 59:59.
You see there's only a few that finish on the start day.
But for those, Wow, we are done. Step 6. Whole days.
We try and figure out the number of net work days from the earlier date to the later date.
If you think about it. Let's say that something came in on Monday.
They worked on it part of Monday. All of Tuesday. All of Wednesday and then part of Thursday.
NETWORKDAYS is going to return four days. So I have to subtract two from that.
Then make sure that I never get a negative number of days.
If it came in on the same day i'm going to end up with a negative 1 there.
Then multiply that by 9 hours like that.
Right. So, that's the whole days. I am almost done. Now the first day is...
so, if it's not the same day take basically midnight minus the time that it came in, although never earlier than 14:59.
For the last day it's the time that they finished minus the start time or 14:59.
And then simply just add everything up.
So K9 plus L9 plus M9 plus N9. And to make Sanjeev's boss happy: Ctrl+1. Use a custom number format of [ h ].mm.
Normally we would put a colon there.
I originally thought about actually trying to figure out the number of hours the number of minutes and building something that would look right but then it's not going to total correctly.
We really need this to be able to total correctly.
So, that's basically my answer.
But, unfortunately, I don't want to send this back to Sanjeev and tell him that you have to use all of these columns all the time.
So then this is just the basic Excel combining formulas.
So, combine the first and last day into a single formula.
See we had IF NOT H9 that was for a first day and the same thing for the last day.
So now it's just a simple formula if not h9 then do the red part do the blue part. So we have one column less.
Put the Adjusted End in the later formula.
So, that had been in E9. It was that formula.
It's referenced twice.
The End Time was referring to E9 and the Adjusted End Time was referring to E9.
Now I've taken that E9 formula and put it into those two formulas.
Then move the Adjusted Start into the adjusted start date and start time. I am not going to go through all these.
You get the basic gist of what's going on here.
Expand the same day formula.
Delete the Same Day: Combine IF.
Make the first and last formula be the Value_If_False clause. In the Same Day.
This was one where we had a formula where we're saying if it's the Same Day then do this otherwise zero.
And then another formula is saying if it's NOT the Same Day, then do this otherwise zero.
So i took the blue clause and the red clause and combined them into one big monster formula.
Delete the Adjusted Start Date column. That had been in D9. It has to go into Whole Day.
So we take that clause and put it in where D9 was. We get that formula.
Delete the Adjusted End Date. That clause goes into D9.
Column D is moving left one column. That's why it always seems like we are working on D9.
Delete the Start Time that had been in D9.
So that blue clause goes in there and there.
We get that long formula for a new "Same Day + First and Last".
Same thing: Delete the end time.
That formula goes in there and off the screen. And then the final formula.
We take the Same Day + First and Last, plus Whole Days.
I had been adding those up in a new column.
Just add them up in one big formula like this. You know, and then I love that email that you write back.
"Hey Sanjeev. Just try this formula here"., as if I was able to just write a formula like that.
And in the old days before Power Query, before LET, before Dynamic Arrays, I think this was pretty much the way that we had to do it.
All right so Mike now let's fast forward from Excel 2016 into what we have today.
What can you come up with? [ Mike Girvin: ] Thanks, MrExcel! Wow, that is a crazy way to have to do time.
I wish they had individual time sheets with each day.
Now, the coolest thing about your solution was the simple fact that you subtracted four hours to get the dates into one day.
That really does make it a lot easier.
Now, I did it in individual pieces before I did the LET function. And I took the whole column.
Instead of using the TIME function, I subtracted a time value (4 divided by 24).
I did that for both columns.
Then we needed just the date so we used INT on the whole column. And it spilled down. We did that there.
We checked the two spilled arrays. Are they the same? Then we use the MOD.
That gets just the time value.
We calculated the number of work days excluding Saturday and Sunday.
And because we have to do individual calculations on the start day (which has a start time), and end day when there's more than one day, we subtracted two.
For time on day one we went ahead and checked is that TRUE?
That means there's only one day and instead of using the MIN and MAX together, I checked for the end time value.
Please give me the one in the middle between 24 hours (which is 1), 15/24 or the actual value.
Then I subtracted the MAX of the start time or 15/24.
Then a zero gets deposited in the cell when there's more than one day. Time for the weekdays. I checked are the weekdays less than one.
If that's true than zero.
Otherwise just take like this 5 right here times the maximum number of hours you can work which is 9 divided by 24.
Time for the first day. I went ahead and checked is it the same?
If it is only one day then we want zero here.
Otherwise we have to take 24 hours and subtract not the start time but the max of the start time or 15/24.
The last day is similar but we're dealing only with the end time so we take the max of the end time or 15/24 and then subtract 15/24. Then we can add the four parts.
Putting it all together in LET().
Well, LET allows us to define a variable There's the name, and then define that variable.
We get to use that variable throughout the rest of the formula.
And it only has to calculate one time. Max Hours.
Adjust time downward four hours. Then I defined Adjusted Start Date.
There's the only cell reference that we are referring to.
Minus the four hours. Same thing for End. Then just Start Date. There's the INT().
Just End Date. We use INT(). Just Start Time. I use the MOD(). Just End Time.
I use the MOD() again. Then we did our logical test.
Is the start equal to the end date?
Then we calculated Number of Weekdays and subtracted two. Then Time when there's only One Day.
We say IF and we use our variable SameDay.
Then the MEDIAN looks at 1, 15/24 or the EndTime.
That gives us the correct end time.
Then we subtract the correct start time.
MAX of our JustStartTime or 15/24, otherwise zero. Then the TimeForWeekdays.
We say IF number of weekdays is less than 1, then 0.
Otherwise number of weekdays times 9 divided by 24, (the max hours).
TimeForFirstDay: If SameDay in this case if it comes out TRUE we want zero otherwise 24 hours minus the MAX of our 15/24 or JustStartTime.
TimeForLastDay we run our same logical test.
Otherwise the correct end time is the MAX of 15/24 or JustEndTime and then we subtract that 15/24.
Finally, TotalHours.
We add everything up and as the only non-defined variable in this whole formula, we'll just display that last defined variable. Then we Enter it and copy it down.
And then, of course, we use that time number formatting with the square brackets. This was a wild one.
I had to get some help from MrExcel.
Build all the pieces in the sheet and then mash it all together. Because look at this: that is ONE column.
That's a BUNCH of columns. Alright I'll throw it back over to MrExcel.
[ Bill Jelen: ] That is a wild formula. The LET() function is new in Microsoft 365.
As I look at my formula and your formula, Mike.
Either way, whoever Sanjeev hands his workbook off to.
Maybe Sanjeev moves on to another job and two years from now.
The next person is going to hate both my solution and your solution because they're just insanely long.
But what I really loved was your use of MEDIAN.
To avoid my nested Min/Max issue.
Yours just gets the one in the middle. That's really brilliant.
And what we really need here is instead of NETWORKDAYS is NETWORKHOURS. It's funny.
I went out to Google. I searched for NETWORKHOURS function Excel.
And while there were several hits.
One of the hits was right at the MrExcel Message Board. Going back to 2013.
SHG and Barry Houdini using that MEDIAN trick.
It is one that I wasn't aware of.
I will have to try and tuck that one away for next time.
You know, Mike i think this is one of those ones where someone watching this video is going to say. "What are you guys doing?
"There's something insanely easy with either Power Query or dynamic arrays.
I kind of had an idea of a Dynamic Array that would look up every minute and some sort of a range version of XLOOKUP or VLOOKUP to check if each minute counted or not.
But I headed down this this method here.
Anyway, Mike: It was great to do another Dueling Excel podcast.
We will have to start to make this a regular thing.
If anyone watching sees a better solution, then please note it down in the YouTube comments.
Give us your ideas on how to make this simpler.
I want to thank Sanjeev for sending this question in. I want to thank you for stopping by.
We'll see you next time for another Dueling Excel Podcast from MrExcel and ExcelisFun.
[ ♫♫ Drumroll ♫ ] [ ♫ ] Formulas? or VBA?
[ ♫ ] [ ♫ ] Ctrl+Shift+Enter or a Macro way?
[ ♫ ] [ ♫ ] Well, there's Mike G on the C.S.E.
[ ♫ ] [ ♫ ] Billy J gettin' down with the VBA! [ ♫ ] [ ♫ ] Oh my! [ ♫ ] [ ♫ ] It's Duelin' Excel Guys! [ ♫ ] [ ♫ ] Stand by! It's the Duelin' Excel guys! [ ♫ ] [ ♫ ] My my! It's Duelin' Excel Time! [ ♫ ] [ applause ]
I am Bill Jelen from MrExcel. I will be joined by Mike Girvin from ExcelisFun.
This is our Episode #191.
NetWorkHours spanning midnight. Hey, welcome back to the Dueling Excel podcast.
This is a question that I'm sure that we've dealt with years ago.
But it would be interesting to see if there are new ways to do it given that we have LET() and Power Query. So, Mike the the question today.
This is from Sanjeev.
He has a start date and time and an end date and time.
He needs to figure out the number of work hours between the two.
The company that Sanjeev works for, their shifts they start on Monday at 5 pm and go to Tuesday at 4 am. This then repeats Tuesday, Wednesday, Thursday, Friday.
This is the thing that really really drives me crazy because that means that we need to check Monday from 5PM to midnight and then Tuesday from midnight to 4AM.
There's a whole bunch of things that we have to check for here and then a little part of Saturday that we have to work - - we have to include as work hours. Now the complication.
Sanjeev doesn't have Microsoft 365. He has Excel 2016. So no LET or Dynamic Arrays.
At least when I was solving it.
And then Sanjeev says, "My company says that three hours and 36 minutes should be reported as 3.36" Well, that's going to drive me crazy all right.
So first thing I realized that having shifts that span midnight might complicate everything because then instead of just five shifts you essentially have 10 shifts because you're breaking them apart.
My first step is: I am going to take all of Sanjeev's times and I am going to subtract four hours and one second from them all.
Then it becomes really simple.
I just have five shifts that I have to worry about from Monday at 3 p.m to midnight.
And then it's just Monday through Friday. So i am going to restate this Mike.
This is my simpler question.
We need a formula to figure out how many hours between 1500 and midnight on Monday through Friday.
NETWORKDAYS() is back in play because we don't have to worry about Saturdays and Sundays now.
Although, it would be really nice if we had a NETWORKHOURS function.
The rules for the duel: use anything in Microsoft 365 Insider's Beta so LET?
Absolutely! XLOOKUP? Go for it! Dynamic arrays? Yes.
and as far as reporting 3.36 to mean 3 hours and 36 minutes. It's just nonsense.
What I am going to do is: I am going to fix that with a custom number format of bracket h bracket dot MM so we're still going to be reporting this as time.
It's just going to look the way that Sanjeev's manager wants it to look. All right. So here's my start.
We have these different examples.
Sanjeev gave me a few of these to trip me up.
I added more to handle all the other things that could trip us up. My plan: Step one.
Back-date everything by four hours. Step 2.
Break out the day from the time using the INT() function. Step 3. Is it the same day?
That seems like a simpler case. So, I want to be able to handle that first.
And now, after the fact, I am wondering if this decision complicated things. If i should have just ignored this.
So I don't want this to cloud your judgment, Mike. Step 4.
Break out the time from the day using MOD(,1). Step 5. Handle the simple case of same day.
It is ending time minus starting time.
But if the starting time is before the work day starts you want to use the later of those two. So the max of those.
Step 6. Then if it's not the same day, how many whole work days are there?
Then multiply that by nine hours a day. Step 7. Then how many hours on the first day?
So, that's midnight minus start time but again start time or 1500 whichever is later. Step 8.
For the last day it's the end time minus 14.59 but it could never be negative.
I don't know why they would ever end it before the workday. I can imagine something where someone stays past four AM.
I don't know. Who knows. Step 9. Sum all the times.
Format with [ H ].MM. Alright.
So I will run through these formulas really quickly.
Take the original time minus four hours and one second. Take the original end time minus four hours and one second.
Simple enough. That's Step 1. Easy! Step 2.
Calculate the adjusted start to a date. So getting rid of the time.
Getting rid of the 14:59 and backing up to 0.00.
I take the INT() the integer function of both of those. Step 3. Is it the same day?
So is this F9 equal to G9?
If I was able to use LET this would be really handy, I think. Okay step 4.
Break out the time from the day. So right here we have the date portion.
Oh, you know now, why didn't I just subtract F9 from D9? That would have been easier.
But instead. I used the MOD function because it makes me look geekier, I guess.
The MOD(D9,1) gets rid of the whole days and gives us just the time. And the end time. Okay now the easy case.
To me it seems easy. If this is the same day.
If H9 is TRUE then we're going to take the end time minus the start time.
But we want to make sure that the end time is never greater than 23:59:59.
And we have to make sure that the start time is never earlier than 14 59:59.
You see there's only a few that finish on the start day.
But for those, Wow, we are done. Step 6. Whole days.
We try and figure out the number of net work days from the earlier date to the later date.
If you think about it. Let's say that something came in on Monday.
They worked on it part of Monday. All of Tuesday. All of Wednesday and then part of Thursday.
NETWORKDAYS is going to return four days. So I have to subtract two from that.
Then make sure that I never get a negative number of days.
If it came in on the same day i'm going to end up with a negative 1 there.
Then multiply that by 9 hours like that.
Right. So, that's the whole days. I am almost done. Now the first day is...
so, if it's not the same day take basically midnight minus the time that it came in, although never earlier than 14:59.
For the last day it's the time that they finished minus the start time or 14:59.
And then simply just add everything up.
So K9 plus L9 plus M9 plus N9. And to make Sanjeev's boss happy: Ctrl+1. Use a custom number format of [ h ].mm.
Normally we would put a colon there.
I originally thought about actually trying to figure out the number of hours the number of minutes and building something that would look right but then it's not going to total correctly.
We really need this to be able to total correctly.
So, that's basically my answer.
But, unfortunately, I don't want to send this back to Sanjeev and tell him that you have to use all of these columns all the time.
So then this is just the basic Excel combining formulas.
So, combine the first and last day into a single formula.
See we had IF NOT H9 that was for a first day and the same thing for the last day.
So now it's just a simple formula if not h9 then do the red part do the blue part. So we have one column less.
Put the Adjusted End in the later formula.
So, that had been in E9. It was that formula.
It's referenced twice.
The End Time was referring to E9 and the Adjusted End Time was referring to E9.
Now I've taken that E9 formula and put it into those two formulas.
Then move the Adjusted Start into the adjusted start date and start time. I am not going to go through all these.
You get the basic gist of what's going on here.
Expand the same day formula.
Delete the Same Day: Combine IF.
Make the first and last formula be the Value_If_False clause. In the Same Day.
This was one where we had a formula where we're saying if it's the Same Day then do this otherwise zero.
And then another formula is saying if it's NOT the Same Day, then do this otherwise zero.
So i took the blue clause and the red clause and combined them into one big monster formula.
Delete the Adjusted Start Date column. That had been in D9. It has to go into Whole Day.
So we take that clause and put it in where D9 was. We get that formula.
Delete the Adjusted End Date. That clause goes into D9.
Column D is moving left one column. That's why it always seems like we are working on D9.
Delete the Start Time that had been in D9.
So that blue clause goes in there and there.
We get that long formula for a new "Same Day + First and Last".
Same thing: Delete the end time.
That formula goes in there and off the screen. And then the final formula.
We take the Same Day + First and Last, plus Whole Days.
I had been adding those up in a new column.
Just add them up in one big formula like this. You know, and then I love that email that you write back.
"Hey Sanjeev. Just try this formula here"., as if I was able to just write a formula like that.
And in the old days before Power Query, before LET, before Dynamic Arrays, I think this was pretty much the way that we had to do it.
All right so Mike now let's fast forward from Excel 2016 into what we have today.
What can you come up with? [ Mike Girvin: ] Thanks, MrExcel! Wow, that is a crazy way to have to do time.
I wish they had individual time sheets with each day.
Now, the coolest thing about your solution was the simple fact that you subtracted four hours to get the dates into one day.
That really does make it a lot easier.
Now, I did it in individual pieces before I did the LET function. And I took the whole column.
Instead of using the TIME function, I subtracted a time value (4 divided by 24).
I did that for both columns.
Then we needed just the date so we used INT on the whole column. And it spilled down. We did that there.
We checked the two spilled arrays. Are they the same? Then we use the MOD.
That gets just the time value.
We calculated the number of work days excluding Saturday and Sunday.
And because we have to do individual calculations on the start day (which has a start time), and end day when there's more than one day, we subtracted two.
For time on day one we went ahead and checked is that TRUE?
That means there's only one day and instead of using the MIN and MAX together, I checked for the end time value.
Please give me the one in the middle between 24 hours (which is 1), 15/24 or the actual value.
Then I subtracted the MAX of the start time or 15/24.
Then a zero gets deposited in the cell when there's more than one day. Time for the weekdays. I checked are the weekdays less than one.
If that's true than zero.
Otherwise just take like this 5 right here times the maximum number of hours you can work which is 9 divided by 24.
Time for the first day. I went ahead and checked is it the same?
If it is only one day then we want zero here.
Otherwise we have to take 24 hours and subtract not the start time but the max of the start time or 15/24.
The last day is similar but we're dealing only with the end time so we take the max of the end time or 15/24 and then subtract 15/24. Then we can add the four parts.
Putting it all together in LET().
Well, LET allows us to define a variable There's the name, and then define that variable.
We get to use that variable throughout the rest of the formula.
And it only has to calculate one time. Max Hours.
Adjust time downward four hours. Then I defined Adjusted Start Date.
There's the only cell reference that we are referring to.
Minus the four hours. Same thing for End. Then just Start Date. There's the INT().
Just End Date. We use INT(). Just Start Time. I use the MOD(). Just End Time.
I use the MOD() again. Then we did our logical test.
Is the start equal to the end date?
Then we calculated Number of Weekdays and subtracted two. Then Time when there's only One Day.
We say IF and we use our variable SameDay.
Then the MEDIAN looks at 1, 15/24 or the EndTime.
That gives us the correct end time.
Then we subtract the correct start time.
MAX of our JustStartTime or 15/24, otherwise zero. Then the TimeForWeekdays.
We say IF number of weekdays is less than 1, then 0.
Otherwise number of weekdays times 9 divided by 24, (the max hours).
TimeForFirstDay: If SameDay in this case if it comes out TRUE we want zero otherwise 24 hours minus the MAX of our 15/24 or JustStartTime.
TimeForLastDay we run our same logical test.
Otherwise the correct end time is the MAX of 15/24 or JustEndTime and then we subtract that 15/24.
Finally, TotalHours.
We add everything up and as the only non-defined variable in this whole formula, we'll just display that last defined variable. Then we Enter it and copy it down.
And then, of course, we use that time number formatting with the square brackets. This was a wild one.
I had to get some help from MrExcel.
Build all the pieces in the sheet and then mash it all together. Because look at this: that is ONE column.
That's a BUNCH of columns. Alright I'll throw it back over to MrExcel.
[ Bill Jelen: ] That is a wild formula. The LET() function is new in Microsoft 365.
As I look at my formula and your formula, Mike.
Either way, whoever Sanjeev hands his workbook off to.
Maybe Sanjeev moves on to another job and two years from now.
The next person is going to hate both my solution and your solution because they're just insanely long.
But what I really loved was your use of MEDIAN.
To avoid my nested Min/Max issue.
Yours just gets the one in the middle. That's really brilliant.
And what we really need here is instead of NETWORKDAYS is NETWORKHOURS. It's funny.
I went out to Google. I searched for NETWORKHOURS function Excel.
And while there were several hits.
One of the hits was right at the MrExcel Message Board. Going back to 2013.
SHG and Barry Houdini using that MEDIAN trick.
It is one that I wasn't aware of.
I will have to try and tuck that one away for next time.
You know, Mike i think this is one of those ones where someone watching this video is going to say. "What are you guys doing?
"There's something insanely easy with either Power Query or dynamic arrays.
I kind of had an idea of a Dynamic Array that would look up every minute and some sort of a range version of XLOOKUP or VLOOKUP to check if each minute counted or not.
But I headed down this this method here.
Anyway, Mike: It was great to do another Dueling Excel podcast.
We will have to start to make this a regular thing.
If anyone watching sees a better solution, then please note it down in the YouTube comments.
Give us your ideas on how to make this simpler.
I want to thank Sanjeev for sending this question in. I want to thank you for stopping by.
We'll see you next time for another Dueling Excel Podcast from MrExcel and ExcelisFun.
[ ♫♫ Drumroll ♫ ] [ ♫ ] Formulas? or VBA?
[ ♫ ] [ ♫ ] Ctrl+Shift+Enter or a Macro way?
[ ♫ ] [ ♫ ] Well, there's Mike G on the C.S.E.
[ ♫ ] [ ♫ ] Billy J gettin' down with the VBA! [ ♫ ] [ ♫ ] Oh my! [ ♫ ] [ ♫ ] It's Duelin' Excel Guys! [ ♫ ] [ ♫ ] Stand by! It's the Duelin' Excel guys! [ ♫ ] [ ♫ ] My my! It's Duelin' Excel Time! [ ♫ ] [ applause ]
