How to calculate pay by month where the contractor worked part of the first and last months. Mike and Bill offer competing formulas but hope you can come up with something better.
Transcript of the video:
Bill: Hey. Welcome back. It’s time for another Dueling Excel podcast. I'm Bill Jelen from MrExcel. I’ll be joined by Mike Girvin from ExcelIsFun. This is our episode 137, percentage of each month.
Alright. So, today's question came in from YouTube. We're trying to figure out how much we pay Joe for each month. Joe, $1500 a month, he started on April or May 14th, left on September 9th, alright, so some sort of a temporary employee, and the way that it was explained to me is we pay him the full month for June, July, August. For September, then, we pay him 9/30 of the month, and for May, we subtract 31-13, figure out how many days he worked, ÷ by 31 * 1500 is how they calculate this.
Alright. So, a few things are relatively straightforward.
We have the months over here. = date value of that month name & in “, space 1 , 2013 which gives us the start of the month. I had to format that as a date. = EO month gives me the end of the month of C5 , 0 so it goes 0 months out, and that gets me the end of the month, and then number of days in the month is D5-C5+1. Simple enough, right?
Here’s where the duel comes in, and we're trying to figure out, like, how we can figure out the number of days in this month that intersect with this period here, and so I think what we're going to do is we're going to take the =MIN of this date, the end date , this ending date for the person, we’ll press F4 to lock that down, and then subtract the MAX of this date, the start date of the month , the start date for the person, F4 , and, in many cases, this is going to end up being negative, alright? So, we'll copy it down, but for the months that the person actually worked, right there, it works except it's off by 1, so we need to add 1 to that whole thing. There we go. Alright, now we need to get rid of the negative, so anytime you want to get rid of the negatives, we take the MAX of 0 , and the rest of the formula, and I think I now have the number of days that this person worked.
So, for June, July, and August, they're getting a 100%, the 1500. For September, 9 ÷ by 30, they're getting 450, and for May, they worked 18 out of the 31 of the month, so they're getting 870. That's the total. The big question, is there a simpler way to do this? Now, my first thought is let's just treat it as a year. So, = this 1500 * 12, what %age of the year was he around? Well, he was around for this many days, the end date – the start date +1, and then we'll ÷ that by 365. So, his pay for that whole period is not the same as the way they’re calculating. Alright.
[ =1500*12 ], [ =D2-C2+1 ], [ =H20/365 ], [ =H21*H19 ].
So, Mike, let's see…I read in your book CTRL+SHIFT+ENTER that array formulas can take a big huge set of formulas like this and replace it with a single formula. That's the goal because, in reality, I'm sure this company has more than just Joe. They would love to have Joe on this line and an answer right here or maybe the months going across. I don't know, but they want to be able to do this for more records, and we don't want to have to have, what, you know, 13 rows * 6, a 100 formulas just to figure out the pay amount.
Mike: Thanks, MrExcel. Wait a second. No, thanks. I have no good solution to this. The best thing you can do is just go rewrite all the employees’ contracts and say, hey, look, we're not going to do it individually by month.
We're going to take, as MrExcel did, the yearly amount, figure out how many days there are with this subtraction…this calculation right here ÷ by 365, and just calculate it that way. So, redo all the contacts, or, number two, you can go post at the MrExcel message board and get Barry Houdini to do one of his magical date formulas, and I've seen him do some stuff. I look at it and I go…and I work my way through it and I cannot get it.
It's just amazing what he could do, or you can do what I'm going to do and make a formula that is way too long. Alright.
I see this as 3 possibilities, right? We could have an end and beginning date in the same month. We could have a begin and ending date with just 2 months or more than 2 months.
So, I'm going to kind of try and build this in different pieces. Well, if it's just this, I'm going to first calculate the %age that this person has worked for this situation, this situation, and this situation. That'll give us 3 formulas, different formulas, and we'll use the choose function to look up 1 of those 3 formulas.
So, the first one is, hey, I'm going to say if it's just both end and start in the same month, I'm going to say, hey, end – begin + 1, and then I need to ÷ it. I need a formula element that can figure out the number of days in this month, total days, so I'm going to say DAY. That gives me 1 through 31 or 1 through 28, and then I'm going to go end of month. So, I’m going to say, hey, give me the end and it doesn't matter because they're both the same. The end of this , 0 says the end of this month. Now, if I were to evaluate this end of month right here, F9, you can see, well, it's a serial number, right, but the day will see it, F9, and it will give me 31, and that's dynamic. Whatever month it is, it'll give us the correct bit. [ =(D3-C3+1)/DAY(EOMONTH(D3,0)) ] Alright. So, that little bit right there and CONTROL+SHIFT ~ or `, that's the keyboard to wipe away any number formatting, it's being sucked from here. So, CONTROL+SHIFT+~ or `, that gives us general number format.
Now, if we have 2 months, I'm going to have to do 2 pieces. I'm going to have to figure out how many days in this month ÷ by the total same there. So, I’m going to say =…well, I need to get the end of the month of this, so I'm going to say end of the month of that date right there , 0. That'll give me the end of the month - this right here. That'll be one day too few, so let's add 1 back in.
Alright. So, that’ll give me the number of the days, and we'll do our same little bit, day end of month of this. That's because we're doing just that little bit there, and if I enter this right here , 0, if I enter this right here, this just gives me just the %age that was worked in that month right there, right? So, I need to add to that, and we're going to have to do a slightly different calculation here because here we want to take the actual date - the last day from the previous month.
So, I'm going to say – an end of month, but instead of saying 0 for this month, I'm going to say -1 for last month. That'll give me the exact…that'll give me the number of days, which is 9, right, and then ÷ by that same little bit, day end of month of this, , 0 ) ). I hope I got it all right there, yeah, and then ). I actually might have one too many (. CONTROL+SHIFT+~. I don't have the right (. It should be right there. That was way too many days, alright? [ =(EOMONTH(C4,0)-C4+1)/DAY(EOMONTH(C4,0))+(D4-EOMONTH(D4,-1))/DAY(EOMONTH(D4,0)) ] So, that is the %age of a month. By the way, we can get any %age here. If it's multiple months, like we're going to get down here, you can get a %age bigger than 100, right?
So, now, this one, well, it's going to be similar to this formula. By the way, I’m staggering it like this because I’m going to have to do something crazy at the end.
Oh, man. This is going to be crazy. I'm going to highlight this, delete this, and put this right here. Alright. So, that gives me this.
I just need to do what? Well, this situation is 2 months but this is >. Well, we only need to calculate the partial for this May and then the 9th month here, but the months in between are a 100 %. So, I need to add the number 1 for each additional month. So, I'm going to say + and then month of the later month. That'll give me 9, – month of this, that'll give me 5, and I have to subtract 1. That will give me 9-5 is 4 -1 is 3. That's the extra month we need to add, and actually I'm going to use this little thing again.
[ =(EOMONTH(C5,0)-C5+1)/DAY(EOMONTH(C5,0))+(D5-EOMONTH(D5,-1))/DAY(EOMONTH(D5,0))+(MONTH(D5)-MONTH(C5)-1) ] So, those are the 3 different pieces. Now, because I'm going to mash them all together right here, I'm actually going to copy this up. I actually built these 2 here with the date scenario because it was easier to build and look at and think about, but now we need to mash them all up into one cell here. So, I'm going to copy this up and I'm going to use…see, they're all looking at the same cell references. Now, I'm going to mash those together, but here's the problem. 1, 2, 3 different formulas. The choose, if it's going to choose between 3 formulas, needs the number 1, 2, 3. I'm going to say how about that?
That gives me…whoops. That's looking at the wrong one. I copied it…so, if I go like this, that gives me -1. I'm not going to need that. So, a 0. This will give me 2 and this will give me something like 4, but guess what?
I need 1, 2, 3. The choose needs a 1 so now I'm going to add 1 but this will give me 1, 2, 5, and I only need 1, 2, 3. So, anything 3 or above has to convert to a 3. So, I'm going to put this inside of LOOKUP. Remember, LOOKUP is like VLOOKUP but it only does approximate match, so this will give me that LOOKUP value, give me 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, whatever it is, , so…whoops. CONTORL+Z. ,. So, that'll give me the value to LOOKUP and then the lookup vector is going to be in array syntax, { 1, 2, 3, and }. It's doing approximate match, so when it gets a bigger one, it's going to take the 3. So, that will give me the 1, 2, 3. [ =LOOKUP(MONTH(D3)-MONTH(C3)+1,{1,2,3}) ] That's going to sit inside of choose and determine which one of these formulas to run to get our %age, total %age. I'm going to copy, CONTROL+C C, open the clipboard, and then get this one, CONTORL+C, and then this one, CONTROL+C. Then, I'm going to come over here, put that LOOKUP inside of CHOOSE. I told you this was the most ridiculous thing ever. There's the index and then we're going to go value 1, that formula , value 2, that formula , value 3, that formula.
So, we're looking at 1 of 3 formulas. CONTROL+ENTER and that just gives me the %ages, Oh man.
Now, I need to take the amount and *.
And this is not an array formula. This is just a really, really ridiculously long formula, a non-Barry Houdini like formula, alright, and I think maybe that's working. Now, if there's not some better solutions posted in the comment sections, then I guess we're not paying attention because there's got to be something better than that. Alright. Throw it back to MrExcel.
Bill: Hey, Mike. Alright. I’ve got to tell you, I'm relieved. When I sent this off to you, I figured you were going to take the ROW of the INDIRECT of the start date : end date, take the MONTH of that, get a unique list of months, count how many times each month occurred, ÷ by a lookup table of the number of days in each month, take those %ages, * the salary, all wrapped in a SUM or SUMPRODUCT, and the thing was going to be 10,000 characters.
So, actually, what you sent is a lot shorter than what I thought you would get. Great way to go. It will be interesting to see in the YouTube comments if someone has something better.
So, I want to thank everyone for stopping by. We'll see you next week for another Dueling Excel podcast from MrExcel and ExcelIsFun.
Alright. So, today's question came in from YouTube. We're trying to figure out how much we pay Joe for each month. Joe, $1500 a month, he started on April or May 14th, left on September 9th, alright, so some sort of a temporary employee, and the way that it was explained to me is we pay him the full month for June, July, August. For September, then, we pay him 9/30 of the month, and for May, we subtract 31-13, figure out how many days he worked, ÷ by 31 * 1500 is how they calculate this.
Alright. So, a few things are relatively straightforward.
We have the months over here. = date value of that month name & in “, space 1 , 2013 which gives us the start of the month. I had to format that as a date. = EO month gives me the end of the month of C5 , 0 so it goes 0 months out, and that gets me the end of the month, and then number of days in the month is D5-C5+1. Simple enough, right?
Here’s where the duel comes in, and we're trying to figure out, like, how we can figure out the number of days in this month that intersect with this period here, and so I think what we're going to do is we're going to take the =MIN of this date, the end date , this ending date for the person, we’ll press F4 to lock that down, and then subtract the MAX of this date, the start date of the month , the start date for the person, F4 , and, in many cases, this is going to end up being negative, alright? So, we'll copy it down, but for the months that the person actually worked, right there, it works except it's off by 1, so we need to add 1 to that whole thing. There we go. Alright, now we need to get rid of the negative, so anytime you want to get rid of the negatives, we take the MAX of 0 , and the rest of the formula, and I think I now have the number of days that this person worked.
So, for June, July, and August, they're getting a 100%, the 1500. For September, 9 ÷ by 30, they're getting 450, and for May, they worked 18 out of the 31 of the month, so they're getting 870. That's the total. The big question, is there a simpler way to do this? Now, my first thought is let's just treat it as a year. So, = this 1500 * 12, what %age of the year was he around? Well, he was around for this many days, the end date – the start date +1, and then we'll ÷ that by 365. So, his pay for that whole period is not the same as the way they’re calculating. Alright.
[ =1500*12 ], [ =D2-C2+1 ], [ =H20/365 ], [ =H21*H19 ].
So, Mike, let's see…I read in your book CTRL+SHIFT+ENTER that array formulas can take a big huge set of formulas like this and replace it with a single formula. That's the goal because, in reality, I'm sure this company has more than just Joe. They would love to have Joe on this line and an answer right here or maybe the months going across. I don't know, but they want to be able to do this for more records, and we don't want to have to have, what, you know, 13 rows * 6, a 100 formulas just to figure out the pay amount.
Mike: Thanks, MrExcel. Wait a second. No, thanks. I have no good solution to this. The best thing you can do is just go rewrite all the employees’ contracts and say, hey, look, we're not going to do it individually by month.
We're going to take, as MrExcel did, the yearly amount, figure out how many days there are with this subtraction…this calculation right here ÷ by 365, and just calculate it that way. So, redo all the contacts, or, number two, you can go post at the MrExcel message board and get Barry Houdini to do one of his magical date formulas, and I've seen him do some stuff. I look at it and I go…and I work my way through it and I cannot get it.
It's just amazing what he could do, or you can do what I'm going to do and make a formula that is way too long. Alright.
I see this as 3 possibilities, right? We could have an end and beginning date in the same month. We could have a begin and ending date with just 2 months or more than 2 months.
So, I'm going to kind of try and build this in different pieces. Well, if it's just this, I'm going to first calculate the %age that this person has worked for this situation, this situation, and this situation. That'll give us 3 formulas, different formulas, and we'll use the choose function to look up 1 of those 3 formulas.
So, the first one is, hey, I'm going to say if it's just both end and start in the same month, I'm going to say, hey, end – begin + 1, and then I need to ÷ it. I need a formula element that can figure out the number of days in this month, total days, so I'm going to say DAY. That gives me 1 through 31 or 1 through 28, and then I'm going to go end of month. So, I’m going to say, hey, give me the end and it doesn't matter because they're both the same. The end of this , 0 says the end of this month. Now, if I were to evaluate this end of month right here, F9, you can see, well, it's a serial number, right, but the day will see it, F9, and it will give me 31, and that's dynamic. Whatever month it is, it'll give us the correct bit. [ =(D3-C3+1)/DAY(EOMONTH(D3,0)) ] Alright. So, that little bit right there and CONTROL+SHIFT ~ or `, that's the keyboard to wipe away any number formatting, it's being sucked from here. So, CONTROL+SHIFT+~ or `, that gives us general number format.
Now, if we have 2 months, I'm going to have to do 2 pieces. I'm going to have to figure out how many days in this month ÷ by the total same there. So, I’m going to say =…well, I need to get the end of the month of this, so I'm going to say end of the month of that date right there , 0. That'll give me the end of the month - this right here. That'll be one day too few, so let's add 1 back in.
Alright. So, that’ll give me the number of the days, and we'll do our same little bit, day end of month of this. That's because we're doing just that little bit there, and if I enter this right here , 0, if I enter this right here, this just gives me just the %age that was worked in that month right there, right? So, I need to add to that, and we're going to have to do a slightly different calculation here because here we want to take the actual date - the last day from the previous month.
So, I'm going to say – an end of month, but instead of saying 0 for this month, I'm going to say -1 for last month. That'll give me the exact…that'll give me the number of days, which is 9, right, and then ÷ by that same little bit, day end of month of this, , 0 ) ). I hope I got it all right there, yeah, and then ). I actually might have one too many (. CONTROL+SHIFT+~. I don't have the right (. It should be right there. That was way too many days, alright? [ =(EOMONTH(C4,0)-C4+1)/DAY(EOMONTH(C4,0))+(D4-EOMONTH(D4,-1))/DAY(EOMONTH(D4,0)) ] So, that is the %age of a month. By the way, we can get any %age here. If it's multiple months, like we're going to get down here, you can get a %age bigger than 100, right?
So, now, this one, well, it's going to be similar to this formula. By the way, I’m staggering it like this because I’m going to have to do something crazy at the end.
Oh, man. This is going to be crazy. I'm going to highlight this, delete this, and put this right here. Alright. So, that gives me this.
I just need to do what? Well, this situation is 2 months but this is >. Well, we only need to calculate the partial for this May and then the 9th month here, but the months in between are a 100 %. So, I need to add the number 1 for each additional month. So, I'm going to say + and then month of the later month. That'll give me 9, – month of this, that'll give me 5, and I have to subtract 1. That will give me 9-5 is 4 -1 is 3. That's the extra month we need to add, and actually I'm going to use this little thing again.
[ =(EOMONTH(C5,0)-C5+1)/DAY(EOMONTH(C5,0))+(D5-EOMONTH(D5,-1))/DAY(EOMONTH(D5,0))+(MONTH(D5)-MONTH(C5)-1) ] So, those are the 3 different pieces. Now, because I'm going to mash them all together right here, I'm actually going to copy this up. I actually built these 2 here with the date scenario because it was easier to build and look at and think about, but now we need to mash them all up into one cell here. So, I'm going to copy this up and I'm going to use…see, they're all looking at the same cell references. Now, I'm going to mash those together, but here's the problem. 1, 2, 3 different formulas. The choose, if it's going to choose between 3 formulas, needs the number 1, 2, 3. I'm going to say how about that?
That gives me…whoops. That's looking at the wrong one. I copied it…so, if I go like this, that gives me -1. I'm not going to need that. So, a 0. This will give me 2 and this will give me something like 4, but guess what?
I need 1, 2, 3. The choose needs a 1 so now I'm going to add 1 but this will give me 1, 2, 5, and I only need 1, 2, 3. So, anything 3 or above has to convert to a 3. So, I'm going to put this inside of LOOKUP. Remember, LOOKUP is like VLOOKUP but it only does approximate match, so this will give me that LOOKUP value, give me 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, whatever it is, , so…whoops. CONTORL+Z. ,. So, that'll give me the value to LOOKUP and then the lookup vector is going to be in array syntax, { 1, 2, 3, and }. It's doing approximate match, so when it gets a bigger one, it's going to take the 3. So, that will give me the 1, 2, 3. [ =LOOKUP(MONTH(D3)-MONTH(C3)+1,{1,2,3}) ] That's going to sit inside of choose and determine which one of these formulas to run to get our %age, total %age. I'm going to copy, CONTROL+C C, open the clipboard, and then get this one, CONTORL+C, and then this one, CONTROL+C. Then, I'm going to come over here, put that LOOKUP inside of CHOOSE. I told you this was the most ridiculous thing ever. There's the index and then we're going to go value 1, that formula , value 2, that formula , value 3, that formula.
So, we're looking at 1 of 3 formulas. CONTROL+ENTER and that just gives me the %ages, Oh man.
Now, I need to take the amount and *.
And this is not an array formula. This is just a really, really ridiculously long formula, a non-Barry Houdini like formula, alright, and I think maybe that's working. Now, if there's not some better solutions posted in the comment sections, then I guess we're not paying attention because there's got to be something better than that. Alright. Throw it back to MrExcel.
Bill: Hey, Mike. Alright. I’ve got to tell you, I'm relieved. When I sent this off to you, I figured you were going to take the ROW of the INDIRECT of the start date : end date, take the MONTH of that, get a unique list of months, count how many times each month occurred, ÷ by a lookup table of the number of days in each month, take those %ages, * the salary, all wrapped in a SUM or SUMPRODUCT, and the thing was going to be 10,000 characters.
So, actually, what you sent is a lot shorter than what I thought you would get. Great way to go. It will be interesting to see in the YouTube comments if someone has something better.
So, I want to thank everyone for stopping by. We'll see you next week for another Dueling Excel podcast from MrExcel and ExcelIsFun.
