Building a calendar tool where Task "B" has to fall N number of days after task A. This particular tool is for scheduling house inspection, title search, and financing after an offer is accepted on a house. But the concepts could apply to any similar set of steps where each step must occur N days after a previous task.
Transcript of the video:
Bill: Hey welcome back, it’s time for another Dueling Excel podcast. I’m Bill Jelen from MrExcel, will be joined by Mike Girvin from ExcelIsFun, this is our episode 168 – Realtor Schedule Tool!
Alright, today’s question via YouTube from John, he’s a real estate agent, he’s trying to figure out a computation of time spreadsheet.
For example “If I have a buyer that has an offer accepted, this begins a time frame, and within 10 days the inspection has to be completed.” And then there’s other events that are depended on out of the inspection, or the original offer acceptance statement.
So how do you set this up?
Alright, I’m probably needlessly complicating this, but let’s start over here with this Events Table that I have created. So, I have listed 9 different events here, I’ve used these codes A-I, but then I put a friendly description here so that way, you know, when we print it the buyer can understand what’s going on. And so, each item has, you know, the description, so the Offer Acceptance.
And then, like, John said “10 days after the Offer Acceptance, the Inspection has to be done.” So this is 10 days after item A. Once we have the inspection done, and I’m making stuff up here, I don’t know, the Title Search has to be done within 7 days after item B. But then the things that the buyer’s supposed to do, like you’re supposed to get your Financing Approved, that has to happen in 25 days, not after the Inspection but after the original Offer Acceptance. Alright, so hey, you better go and start getting financing immediately and don’t wait for the inspections.
So you see that each item here has, let’s call it, a predecessor, and it’s some number of days after that predecessor. And, to further complicate things, because some of these things are based on banks, I am using business days instead of just straight days, that might add a complication that we don’t really need. So, because I’m going to use business days, I have a list here of all the holidays, the Holiday Table. So these two tables are off to the right-hand side where’s no-one going to see them, and we can come back here to build the actual item.
So here’s what I have, I have columns from the event code, the description, the date that I thing that should happen, and then sometimes we’ll fill in the actual date, alright. So, you want to use the forecast date until we have an actual date, actually what I really want to do, when you get right down to it, I want to use the last day to occur in this tiny little row of 2 cells.
Alright, I don’t want to use the MIN, I don’t want to use the MAX, I want to use the last day that I find because that’s the good date. And so here’s the first Excel trick in this whole thing.
I’m using HLOOKUP into this tiny little range of 2 cells, looking up a date that is larger than any possible date into that range, I’m looking for the first row, and I’m doing a ,TRUE , alright. So, this ,TRUE version of HLOOKUP is the weird one, when it can’t find a number that is smaller than this or larger than this, it just gives up and gives me the last number that it can find, so it’s giving me 11/21. And, I want you to watch here, this is 12/8, if I change this to a different date, 12/6/14, alright, it updates to show 12/6/14. Or if I change it to a later date, 12/10/14, it updates to show me the later dates. So it’s not a MIN or a MAX, it’s just saying “Hey, you know, whatever the date in this tiny row of 2 cells, is the date to use.” Alright, for item A there are no predecessors so I just blacked those out, we don’t fill them in, alright. And then, we’re going to come down here to the second event.
And you see, this is just formulas that are pointing up here, same thing, formulas are pointing up here, I copy those down, so you change things in the table, they will change here as well. Alright now, the forecast date is based on these calculations, so what’s the predecessor for item B? We do a VLOOKUP for item B, come out here to the 4th column, and say “The predecessor is A.” Alright, so that’s our first test, then, what was the date for item A? So I lookup item A into this red table back here, I’m looking for the 5th column, the date to use, ,0 I’m doing an exact match, same as doing a ,FALSE.
And then the delay is based on this table up here, we’re getting the 3rd column, and again an exact match.
Alright, so once I know that this item happened after item A, this is the best guess we have for item A, and this is the number of days, it’s just a simple matter of adding 10 workdays to 11/21. How do we get workdays? Use the WORKDAY function. So, starting from H17, we want to go out 10 workdays, and oh, by the way, these holidays don’t count into the calculation. Alright so, once I got row 2 done, just copy it down to all the other rows and it seems to work.
Alright so here, the buyer has until December 8th to get the inspection done. Good news, the inspector came out, the buyer says GO, and that happened on the 5th, watch a lot of these dates down here automatically change.
Well some of the days change, the things that are still dependent on item A, they remain where they were. Alright so, that’s a very complicated way of doing this, Mike, I’m interested to see what approach you take!
Mike: Thanks MrExcel! Oh man, now I know why they call YOU MrExcel! There’s just no way!
How did you figure this all out from that question? Hey, look what I did, I read this question and I thought they wanted the start date at #10, and they just wanted a list of dates! So there’s the formula and I copied it down, and just gives me a list of dates, if I type 15 BOOM! Oh man! But uh, I guess, now that I re-read this question 30 times and watched your video segment 5 times, I think I sort-of get it. Now uh, let’s just delete all this and see my way of doing this.
So not the same way as yours at all, I’m still not quite sure how you got so smart.
But here I’m just going to take, I have a column, here’s the category, here’s the previous, and here’s the Key. So all I did is I looked over to the previous and said “Oh, I have to go get the date from before, that’s the offer date.” And then I have to go up to this table, find B, and then get 10, so that’s what I did, and so I manually built, right? So this one says “I got to go get the B date, and then go up and get the C from this table, so I’m adding 7.” Here, I have to- oh, I have to look up all the way up to A, and the go up and get D days, right? Man, so you can do that, I guess that would work, right? So, it’s a little bit of a hassle, even when I did this by hand, I made all sorts of mistakes.
So maybe we could try and figure out the logic, which is basically this. I’m always going to- I’m in this row, I’m going to look up the B to get the date from above! So down here, let’s say right here, I need to look at that A, but then remember to go up to the top to get the A date. And then I’m going to do a second look up, it goes up here, finds this category from the Key column, and gets the number! Alright, so you’re ready, =VLOOKUP, we’ll try VLOOKUP to look up the date above.
So I’m going to look up BOOM, that’s going to be the previous, comma, the lookup table is going to be just like that. But I’m going to have to lock up the first, F4, because it’s going to expand as I go down, comma, and then I have to make sure I get this right, so the 5th column. And I’m not putting anything for lookup range because this is alphabetical, it is sorted! Close parenthesis, so I accept the default which is TRUE. That’s not going to work, but that will give me the date, alright so, and if I copy this down you couldn’t see anything because it’ll all be 11/26.
But now! I’m going to + in the VLOOKUP the actual number of days I need to add.
So I’m actually NOW looking up the actual Key, comma, the table will be just to the third column, and I’m noticing it’s sorted, F4 to lock it, ,3 , it’s sorted so I’m leaving it out, Ctrl+Enter, and double-click and send it down. That definitely does not do what MrExcel does, which is, you know, taking into consideration that you might actually have an early date, but does create this list of dates from this information up here. Now if we needed to do it with WEEKDAY, we’d start off with the WORKDAY, and I’m going to do the INTL, that’s 2010 or later. The start date, well for right now I’m just going to click there, I’m going to come back and fix it in a second, and the days well, I know it’s to A, so I got to go get- I mean for the B, I have to go get the 10, comma, I’m going to assume it’s whatever, I’m going to say 1 which is the default but we’ll put 1 in, and then the holidays, F4, close parenthesis.
So, I’m going to notice that maybe I could put a VLOOKUP there to get the date from above, and then VLOOKUP to go and get that, so watch this. I’m going to come over here and I’m going to cheat, because I can’t type very well, I’m in Edit mode, right? I’m actually going to change the formula for a second, because now, when I paste this over here it wouldn’t work because I need to look at the workday column. And I’m going to change it to 6, and I’m going to highlight, Ctrl+C C to open up the clipboard, and then I’m going to highlight this and it’ll work just fine, Ctrl+C, now I’ve loaded up the clipboard, Esc. By escaping I get back, I didn’t, you know, keep that 6 in that expanded range.
Now I’m going to come over here, double-click the start date, and I can’t see but I think that was this one right here, and then double-click the number of days, that better be the one that’s looking up here, and sure enough it is. Alright I got my fingers crossed, Ctrl+Enter, double-click and send it down, wow, that is insane! MrExcel, how did you figure all this out? Have you done real estate or something like that? Alright, throwback to MrExcel!
Bill: Hey, alright Mike, there were a lot of cool tips in there, I love how you can just open that clipboard pane with the shortcut key, that’s really, really cool but. You know, I need to clarify one thing that you said, right around the 8:10 mark, you know, how you’re finishing up that VLOOKUP, and you said “Hey, I’m not putting anything for the 4th argument, the lookup range argument, because this lookup table is sorted!” Now, when you don’t put up anything, that’s not saying that we don’t care whether it’s FALSE or TRUE, it’s explicitly saying that you’re doing the TRUE. And you’re right, you’re generally OK with the data being sorted, but you’re also going to assume that there’s never going to be a miss. In this case you had A-H, they’re all there but, you know, what if someone accidentally typed the wrong letter, they meant to type a D and they hit an S? Well now, it’s going to give you an answer as if it found something, when in fact it didn’t, so, just want to clarify that the rule for choosing TRUE or FALSE.
There’s good reasons for choosing TRUE, but it’s not because the table is sorted, it has to be sorted, and you know that you’re never going to have a miss or anything like that, so. Been talking about VLOOKUP TRUEs a lot in my power Excel seminar and uh, so just wanted to clarify that. But hey, this is great to be doing Dueling podcasts again, I’ve been on the road, right now I’m down in Florida for the Orion rocket launch as part of the NASA social media program. So I’m out having fun, and so uh, this took a while, primarily because of me.
Well hey, I want to thank everyone for stopping by, we’ll see you next time for another Dueling Excel podcast from MrExcel and ExcelIsFun!
Alright, today’s question via YouTube from John, he’s a real estate agent, he’s trying to figure out a computation of time spreadsheet.
For example “If I have a buyer that has an offer accepted, this begins a time frame, and within 10 days the inspection has to be completed.” And then there’s other events that are depended on out of the inspection, or the original offer acceptance statement.
So how do you set this up?
Alright, I’m probably needlessly complicating this, but let’s start over here with this Events Table that I have created. So, I have listed 9 different events here, I’ve used these codes A-I, but then I put a friendly description here so that way, you know, when we print it the buyer can understand what’s going on. And so, each item has, you know, the description, so the Offer Acceptance.
And then, like, John said “10 days after the Offer Acceptance, the Inspection has to be done.” So this is 10 days after item A. Once we have the inspection done, and I’m making stuff up here, I don’t know, the Title Search has to be done within 7 days after item B. But then the things that the buyer’s supposed to do, like you’re supposed to get your Financing Approved, that has to happen in 25 days, not after the Inspection but after the original Offer Acceptance. Alright, so hey, you better go and start getting financing immediately and don’t wait for the inspections.
So you see that each item here has, let’s call it, a predecessor, and it’s some number of days after that predecessor. And, to further complicate things, because some of these things are based on banks, I am using business days instead of just straight days, that might add a complication that we don’t really need. So, because I’m going to use business days, I have a list here of all the holidays, the Holiday Table. So these two tables are off to the right-hand side where’s no-one going to see them, and we can come back here to build the actual item.
So here’s what I have, I have columns from the event code, the description, the date that I thing that should happen, and then sometimes we’ll fill in the actual date, alright. So, you want to use the forecast date until we have an actual date, actually what I really want to do, when you get right down to it, I want to use the last day to occur in this tiny little row of 2 cells.
Alright, I don’t want to use the MIN, I don’t want to use the MAX, I want to use the last day that I find because that’s the good date. And so here’s the first Excel trick in this whole thing.
I’m using HLOOKUP into this tiny little range of 2 cells, looking up a date that is larger than any possible date into that range, I’m looking for the first row, and I’m doing a ,TRUE , alright. So, this ,TRUE version of HLOOKUP is the weird one, when it can’t find a number that is smaller than this or larger than this, it just gives up and gives me the last number that it can find, so it’s giving me 11/21. And, I want you to watch here, this is 12/8, if I change this to a different date, 12/6/14, alright, it updates to show 12/6/14. Or if I change it to a later date, 12/10/14, it updates to show me the later dates. So it’s not a MIN or a MAX, it’s just saying “Hey, you know, whatever the date in this tiny row of 2 cells, is the date to use.” Alright, for item A there are no predecessors so I just blacked those out, we don’t fill them in, alright. And then, we’re going to come down here to the second event.
And you see, this is just formulas that are pointing up here, same thing, formulas are pointing up here, I copy those down, so you change things in the table, they will change here as well. Alright now, the forecast date is based on these calculations, so what’s the predecessor for item B? We do a VLOOKUP for item B, come out here to the 4th column, and say “The predecessor is A.” Alright, so that’s our first test, then, what was the date for item A? So I lookup item A into this red table back here, I’m looking for the 5th column, the date to use, ,0 I’m doing an exact match, same as doing a ,FALSE.
And then the delay is based on this table up here, we’re getting the 3rd column, and again an exact match.
Alright, so once I know that this item happened after item A, this is the best guess we have for item A, and this is the number of days, it’s just a simple matter of adding 10 workdays to 11/21. How do we get workdays? Use the WORKDAY function. So, starting from H17, we want to go out 10 workdays, and oh, by the way, these holidays don’t count into the calculation. Alright so, once I got row 2 done, just copy it down to all the other rows and it seems to work.
Alright so here, the buyer has until December 8th to get the inspection done. Good news, the inspector came out, the buyer says GO, and that happened on the 5th, watch a lot of these dates down here automatically change.
Well some of the days change, the things that are still dependent on item A, they remain where they were. Alright so, that’s a very complicated way of doing this, Mike, I’m interested to see what approach you take!
Mike: Thanks MrExcel! Oh man, now I know why they call YOU MrExcel! There’s just no way!
How did you figure this all out from that question? Hey, look what I did, I read this question and I thought they wanted the start date at #10, and they just wanted a list of dates! So there’s the formula and I copied it down, and just gives me a list of dates, if I type 15 BOOM! Oh man! But uh, I guess, now that I re-read this question 30 times and watched your video segment 5 times, I think I sort-of get it. Now uh, let’s just delete all this and see my way of doing this.
So not the same way as yours at all, I’m still not quite sure how you got so smart.
But here I’m just going to take, I have a column, here’s the category, here’s the previous, and here’s the Key. So all I did is I looked over to the previous and said “Oh, I have to go get the date from before, that’s the offer date.” And then I have to go up to this table, find B, and then get 10, so that’s what I did, and so I manually built, right? So this one says “I got to go get the B date, and then go up and get the C from this table, so I’m adding 7.” Here, I have to- oh, I have to look up all the way up to A, and the go up and get D days, right? Man, so you can do that, I guess that would work, right? So, it’s a little bit of a hassle, even when I did this by hand, I made all sorts of mistakes.
So maybe we could try and figure out the logic, which is basically this. I’m always going to- I’m in this row, I’m going to look up the B to get the date from above! So down here, let’s say right here, I need to look at that A, but then remember to go up to the top to get the A date. And then I’m going to do a second look up, it goes up here, finds this category from the Key column, and gets the number! Alright, so you’re ready, =VLOOKUP, we’ll try VLOOKUP to look up the date above.
So I’m going to look up BOOM, that’s going to be the previous, comma, the lookup table is going to be just like that. But I’m going to have to lock up the first, F4, because it’s going to expand as I go down, comma, and then I have to make sure I get this right, so the 5th column. And I’m not putting anything for lookup range because this is alphabetical, it is sorted! Close parenthesis, so I accept the default which is TRUE. That’s not going to work, but that will give me the date, alright so, and if I copy this down you couldn’t see anything because it’ll all be 11/26.
But now! I’m going to + in the VLOOKUP the actual number of days I need to add.
So I’m actually NOW looking up the actual Key, comma, the table will be just to the third column, and I’m noticing it’s sorted, F4 to lock it, ,3 , it’s sorted so I’m leaving it out, Ctrl+Enter, and double-click and send it down. That definitely does not do what MrExcel does, which is, you know, taking into consideration that you might actually have an early date, but does create this list of dates from this information up here. Now if we needed to do it with WEEKDAY, we’d start off with the WORKDAY, and I’m going to do the INTL, that’s 2010 or later. The start date, well for right now I’m just going to click there, I’m going to come back and fix it in a second, and the days well, I know it’s to A, so I got to go get- I mean for the B, I have to go get the 10, comma, I’m going to assume it’s whatever, I’m going to say 1 which is the default but we’ll put 1 in, and then the holidays, F4, close parenthesis.
So, I’m going to notice that maybe I could put a VLOOKUP there to get the date from above, and then VLOOKUP to go and get that, so watch this. I’m going to come over here and I’m going to cheat, because I can’t type very well, I’m in Edit mode, right? I’m actually going to change the formula for a second, because now, when I paste this over here it wouldn’t work because I need to look at the workday column. And I’m going to change it to 6, and I’m going to highlight, Ctrl+C C to open up the clipboard, and then I’m going to highlight this and it’ll work just fine, Ctrl+C, now I’ve loaded up the clipboard, Esc. By escaping I get back, I didn’t, you know, keep that 6 in that expanded range.
Now I’m going to come over here, double-click the start date, and I can’t see but I think that was this one right here, and then double-click the number of days, that better be the one that’s looking up here, and sure enough it is. Alright I got my fingers crossed, Ctrl+Enter, double-click and send it down, wow, that is insane! MrExcel, how did you figure all this out? Have you done real estate or something like that? Alright, throwback to MrExcel!
Bill: Hey, alright Mike, there were a lot of cool tips in there, I love how you can just open that clipboard pane with the shortcut key, that’s really, really cool but. You know, I need to clarify one thing that you said, right around the 8:10 mark, you know, how you’re finishing up that VLOOKUP, and you said “Hey, I’m not putting anything for the 4th argument, the lookup range argument, because this lookup table is sorted!” Now, when you don’t put up anything, that’s not saying that we don’t care whether it’s FALSE or TRUE, it’s explicitly saying that you’re doing the TRUE. And you’re right, you’re generally OK with the data being sorted, but you’re also going to assume that there’s never going to be a miss. In this case you had A-H, they’re all there but, you know, what if someone accidentally typed the wrong letter, they meant to type a D and they hit an S? Well now, it’s going to give you an answer as if it found something, when in fact it didn’t, so, just want to clarify that the rule for choosing TRUE or FALSE.
There’s good reasons for choosing TRUE, but it’s not because the table is sorted, it has to be sorted, and you know that you’re never going to have a miss or anything like that, so. Been talking about VLOOKUP TRUEs a lot in my power Excel seminar and uh, so just wanted to clarify that. But hey, this is great to be doing Dueling podcasts again, I’ve been on the road, right now I’m down in Florida for the Orion rocket launch as part of the NASA social media program. So I’m out having fun, and so uh, this took a while, primarily because of me.
Well hey, I want to thank everyone for stopping by, we’ll see you next time for another Dueling Excel podcast from MrExcel and ExcelIsFun!
