Episode #1388 comes from contacts on Facebook: A Two condition formula to check if a date is today...another way to select a hyperlink cell without activating the hyperlink...why boolean formulas sometimes return 0 or 1 instead of False or True.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode 1388.
Project Due.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
I wanna clear up something from episode 1360 that's back when we talked about how to select a cell that contains a hyperlink, and I had kind of said that you had to sneak in using the arrow keys turns out that is not true.
Bob from Kentucky sent in a great tip in wins in Excel Master pen.
He says just go click on the cell and see it starts out with the hand hold for about two seconds when changes from the hand of the plus let go and you've now effectively selected that cell.
So, great this question came in from someone posted on my Facebook wall or sent me a Facebook message.
They have a series of due dates.
Now, Today is April 21st and they have a great little formula over here a Boolean formula that says if e 2 minus today, is less than 1 then this thing is due, right?
I love that formula and hey, by the way here's a little puzzle for you. You must watch over to another Excel.
There we go. So, here's a similar format they wanna see a four percent is equal to two percent and so, we do that little check and normally that would either report true or false, but check it out.
What's up with this. we're getting zero and if they do happen to be the same 4 percent we're getting 1, 0's and 1's instead of trues and falses.
Ever seen it like that I went back to the person send it to me and I said all right let me guess this spreadsheet started its life in Lotus 123 that's exactly right.
How'd you know that's just one of those weird things if something even if it was in lotus like 20 years ago, and you've been updating it in Excel the whole time.
I have no idea where the heck Microsoft stores that bid for.
Hey, this is a lotus spreadsheet, but anyway back to the question from, back to the question sent in by Facebook.
Hey, we have this new column. Now, that says they mark if something is paid, and so, if it's been paid now, we don't need a hassle.
We don't want this to be true, and so I said okay, take your current formula, and we're going to wrap the AND function, AND function around that formula.
So, equal AND open parentheses and then we'll go to the end of the formula they have and now we need to check and see if In this case column F is equal to a Y.
Now, I notice here they use upper case Y and lower case Y.
So, we could say if the lower of F2, is not equal to lowercase y.
Shoot that down and what we should see there is the items from today that have already been paid are now mark as false.
If you're sure that no one's going to put ends in here or accidentally put a y and then space that y out with a space if you're absolutely sure that, things are going to be blank then rather than checking for the lower of F2 you could just say.
So, that first thing has to be true, and then also is blank F2.
Is blank is looking for a completely empty cell.
So, you in that case there where I space something out, and it looks like it's an empty cell, that's really not an empty cell and those days for all 4/21.
Today, they're still going to be reported as not due because of that space there.
So, you know you just have to decide are all the Y's capitalized or although Y's not capitalized.
You know any chance someone's going to put a space in there and figure out the best way to check for that over there.
So, three things today the weird Lotus 123.
If you know where that bit is stored, that'd be great also how to select a cell that has a hyperlink.
Thanks to Bob from Kentucky for that and then taking a Boolean formula and checking for Okay oh hey, I wanna thank you for stopping by.
We'll see you next time for another netcast MrExcel.
Learn Excel from MrExcel podcast episode 1388.
Project Due.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
I wanna clear up something from episode 1360 that's back when we talked about how to select a cell that contains a hyperlink, and I had kind of said that you had to sneak in using the arrow keys turns out that is not true.
Bob from Kentucky sent in a great tip in wins in Excel Master pen.
He says just go click on the cell and see it starts out with the hand hold for about two seconds when changes from the hand of the plus let go and you've now effectively selected that cell.
So, great this question came in from someone posted on my Facebook wall or sent me a Facebook message.
They have a series of due dates.
Now, Today is April 21st and they have a great little formula over here a Boolean formula that says if e 2 minus today, is less than 1 then this thing is due, right?
I love that formula and hey, by the way here's a little puzzle for you. You must watch over to another Excel.
There we go. So, here's a similar format they wanna see a four percent is equal to two percent and so, we do that little check and normally that would either report true or false, but check it out.
What's up with this. we're getting zero and if they do happen to be the same 4 percent we're getting 1, 0's and 1's instead of trues and falses.
Ever seen it like that I went back to the person send it to me and I said all right let me guess this spreadsheet started its life in Lotus 123 that's exactly right.
How'd you know that's just one of those weird things if something even if it was in lotus like 20 years ago, and you've been updating it in Excel the whole time.
I have no idea where the heck Microsoft stores that bid for.
Hey, this is a lotus spreadsheet, but anyway back to the question from, back to the question sent in by Facebook.
Hey, we have this new column. Now, that says they mark if something is paid, and so, if it's been paid now, we don't need a hassle.
We don't want this to be true, and so I said okay, take your current formula, and we're going to wrap the AND function, AND function around that formula.
So, equal AND open parentheses and then we'll go to the end of the formula they have and now we need to check and see if In this case column F is equal to a Y.
Now, I notice here they use upper case Y and lower case Y.
So, we could say if the lower of F2, is not equal to lowercase y.
Shoot that down and what we should see there is the items from today that have already been paid are now mark as false.
If you're sure that no one's going to put ends in here or accidentally put a y and then space that y out with a space if you're absolutely sure that, things are going to be blank then rather than checking for the lower of F2 you could just say.
So, that first thing has to be true, and then also is blank F2.
Is blank is looking for a completely empty cell.
So, you in that case there where I space something out, and it looks like it's an empty cell, that's really not an empty cell and those days for all 4/21.
Today, they're still going to be reported as not due because of that space there.
So, you know you just have to decide are all the Y's capitalized or although Y's not capitalized.
You know any chance someone's going to put a space in there and figure out the best way to check for that over there.
So, three things today the weird Lotus 123.
If you know where that bit is stored, that'd be great also how to select a cell that has a hyperlink.
Thanks to Bob from Kentucky for that and then taking a Boolean formula and checking for Okay oh hey, I wanna thank you for stopping by.
We'll see you next time for another netcast MrExcel.
