The Criteria: "If the Date is within the Taget Range, Color the Cell Green; If the Date is previous to the Target Range, Color the Cell Red and show the Number of Days Previous to the Target Range; If the Date is Greater than the Target Range, Color the Cell Blue and show the Number of Days forward from the Target Range." Uh... Really? Ok, well - as always - MrExcel comes to the rescue with a viable solution to reach the objective. Follow along with Episode #1696 as Bill shows us how to achieve the results.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
MrExcel podcast is sponsored by "Easy-XL"!
Learn Excel from MrExcel podcast, episode 1696 - Is Date in 13-17 May?
Well, today's question is sent in by Noel.
Sometimes I think people make this stuff up, I mean initially, I saw this and I'm like "Noel, no way this is, this is going to be impossible." But Noel swears that she didn't set the date up this way, she's getting the data from someone else. Here's the problem: we have a target range 13-17 space May, and we're supposed to tell if this date is in that target range. If it's in the target range, we color the cell green, if it's less than the target range, show the cell is red, and the number of days before. So, from May 13th back to April 9th, if the data is greater than the target, show the cell is blue, in the number of days after. Alright, so I'm starting to think through this, and then I see this cell right here, this 29-3 space May, what does that mean? That's April 29th through May 3rd! Who's the genius over in IT who took the time to format something like THIS, I wondered? Alright, so I started out heading down this path.
The COMPARETO will just copy that date over, and then we need to figure out where the dash is here, because it's not always in the 3rd position, sometimes it's in the 2nd position.
So where's the dash? So =FIND the dash in A2. Now that I know where that dash is, I want to start at the position just after that, and get the rest of the characters, which will get me the ending data's text. So the MID of A2, D2+1 is where the start. I just grabbed 50 characters just because I'm lazy, I didn't want to use the length, but then I have to use the trim to get rid of the extra spaces, or maybe I don't, well I didn't, OK.
Then, ending data is a date. So we take this text, 17 space May, append it with a space, and the YEAR(TODAY()), throw that whole thing through the date value, and now I finally have the ending date. Alright now, if it wasn't for 29-3 space May, I wouldn't have to do these whole next steps: The day of the ending dates =DAY(F2), the day of the beginning date is going to be the LEFT of A2, this number -1, and we're going to add 0 to that to convert it to a date.
So LEFT(A2,D2-1). And then check and see if, it's the strange one, where the beginning day is greater than the ending day. Alright, so our beginning date is, we're to use the DATE function, the YEAR of F2, I could have used the year of today. The MONTH of F2, but, if this is TRUE, then we need to -1 from them, to get back to the previous month, otherwise 0. And then finally, the day portion is what's stored here in H2. So all of these steps just to get my ending date and my beginning date.
Alright, then we want to have, remember, green, red, and blue. So, I'm not sure that this is the best way to go, but it's what I did.
IF C2<J2, then subtract them, that way I'm going to get a negative number. And then over here, if it's greater, then subtract them again, but this time I'm subtracting from the ending date, that way I can see how far beyond the target. And then just simply add these up. So negative numbers are going to be red, positive number is going to be blue, numbers that are 0, so neither of those are TRUE, then we know that we're within our range.
So the Conditional Formatting here, Alt O D, if cell value is greater than 0 use blue, if cell value is less than zero use red. Before I apply that, I turned everything green. And then finally, to get the zeros to not even appear, Ctrl+1, Custom Number Format of 0, -0. I actually bet Noel wants to take that minus sign out, but I put it in just so I could see this is working. And then ;; to hide the zeros, alright now.
So, good news, hey, it could be done without having to go back to the people who did this, and make them redo it, because you know, those IT folks, they're busy, they'll say "It'll take four weeks, just to get us an estimate." And then 4 months to get the thing done, if your IT department's like my IT department.
Alright, so we have whole thing, now that the scary thing is, when Noel sent me the spreadsheet, I think that she wants to do this in 4 different columns, she has 4 different sets of dates to compare, so. You know, this is going to be really, really ugly. And I actually sent this to Noel, with an apology "Wow, this is really ugly", and then I started to think about it.
You know, these dates over here, the 13-17 dash May. So I looked at the ending date, minus the beginning date, and it turns out that in every single case, it's 4. So these are weeks, this is a week range here. It's Monday-Friday actually. Actually simplifies things a lot, we get our ending data's text, ending date as a date, and then the beginning data is really simple, it's just E2-4. So if it's TRUE, that these are always going to be Monday-Friday dates. Well yeah, then hey, this is just a tiny, tiny bit simpler, with our result there. I combine those two IF statements, just add them together, and we get our result. What an ugly, horrible problem. And if it wasn't for the fact that Noel was forced to solve this problem, because someone in IT was giving her a really strange data as the input, I would have just laughed this off as a cruel joke. Right, I mean, just seeing if you can make my head explode. But here we go, the answer, I'm sure we could work to combine this for, you know, all of these formulas into a single formula here.
Alright well hey, I want to thank Noel for sending that question in, and I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Learn Excel from MrExcel podcast, episode 1696 - Is Date in 13-17 May?
Well, today's question is sent in by Noel.
Sometimes I think people make this stuff up, I mean initially, I saw this and I'm like "Noel, no way this is, this is going to be impossible." But Noel swears that she didn't set the date up this way, she's getting the data from someone else. Here's the problem: we have a target range 13-17 space May, and we're supposed to tell if this date is in that target range. If it's in the target range, we color the cell green, if it's less than the target range, show the cell is red, and the number of days before. So, from May 13th back to April 9th, if the data is greater than the target, show the cell is blue, in the number of days after. Alright, so I'm starting to think through this, and then I see this cell right here, this 29-3 space May, what does that mean? That's April 29th through May 3rd! Who's the genius over in IT who took the time to format something like THIS, I wondered? Alright, so I started out heading down this path.
The COMPARETO will just copy that date over, and then we need to figure out where the dash is here, because it's not always in the 3rd position, sometimes it's in the 2nd position.
So where's the dash? So =FIND the dash in A2. Now that I know where that dash is, I want to start at the position just after that, and get the rest of the characters, which will get me the ending data's text. So the MID of A2, D2+1 is where the start. I just grabbed 50 characters just because I'm lazy, I didn't want to use the length, but then I have to use the trim to get rid of the extra spaces, or maybe I don't, well I didn't, OK.
Then, ending data is a date. So we take this text, 17 space May, append it with a space, and the YEAR(TODAY()), throw that whole thing through the date value, and now I finally have the ending date. Alright now, if it wasn't for 29-3 space May, I wouldn't have to do these whole next steps: The day of the ending dates =DAY(F2), the day of the beginning date is going to be the LEFT of A2, this number -1, and we're going to add 0 to that to convert it to a date.
So LEFT(A2,D2-1). And then check and see if, it's the strange one, where the beginning day is greater than the ending day. Alright, so our beginning date is, we're to use the DATE function, the YEAR of F2, I could have used the year of today. The MONTH of F2, but, if this is TRUE, then we need to -1 from them, to get back to the previous month, otherwise 0. And then finally, the day portion is what's stored here in H2. So all of these steps just to get my ending date and my beginning date.
Alright, then we want to have, remember, green, red, and blue. So, I'm not sure that this is the best way to go, but it's what I did.
IF C2<J2, then subtract them, that way I'm going to get a negative number. And then over here, if it's greater, then subtract them again, but this time I'm subtracting from the ending date, that way I can see how far beyond the target. And then just simply add these up. So negative numbers are going to be red, positive number is going to be blue, numbers that are 0, so neither of those are TRUE, then we know that we're within our range.
So the Conditional Formatting here, Alt O D, if cell value is greater than 0 use blue, if cell value is less than zero use red. Before I apply that, I turned everything green. And then finally, to get the zeros to not even appear, Ctrl+1, Custom Number Format of 0, -0. I actually bet Noel wants to take that minus sign out, but I put it in just so I could see this is working. And then ;; to hide the zeros, alright now.
So, good news, hey, it could be done without having to go back to the people who did this, and make them redo it, because you know, those IT folks, they're busy, they'll say "It'll take four weeks, just to get us an estimate." And then 4 months to get the thing done, if your IT department's like my IT department.
Alright, so we have whole thing, now that the scary thing is, when Noel sent me the spreadsheet, I think that she wants to do this in 4 different columns, she has 4 different sets of dates to compare, so. You know, this is going to be really, really ugly. And I actually sent this to Noel, with an apology "Wow, this is really ugly", and then I started to think about it.
You know, these dates over here, the 13-17 dash May. So I looked at the ending date, minus the beginning date, and it turns out that in every single case, it's 4. So these are weeks, this is a week range here. It's Monday-Friday actually. Actually simplifies things a lot, we get our ending data's text, ending date as a date, and then the beginning data is really simple, it's just E2-4. So if it's TRUE, that these are always going to be Monday-Friday dates. Well yeah, then hey, this is just a tiny, tiny bit simpler, with our result there. I combine those two IF statements, just add them together, and we get our result. What an ugly, horrible problem. And if it wasn't for the fact that Noel was forced to solve this problem, because someone in IT was giving her a really strange data as the input, I would have just laughed this off as a cruel joke. Right, I mean, just seeing if you can make my head explode. But here we go, the answer, I'm sure we could work to combine this for, you know, all of these formulas into a single formula here.
Alright well hey, I want to thank Noel for sending that question in, and I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
