In todays podcast, Episode 552, I use the IF function to hide values that are negative. This is a formula-based solution. In tomorrows podcast, a different way to solve the problem using number formatting.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Alright welcome back to the MrExcel netcast, I'm Bill Jelen.
In yesterday's netcast we had a suggestion from Van on how to simplify the birthday suggestion and in the interest of time I simplified his suggestion and we created a formula yesterday that took a look at a date in column C –TPDAY, the today function to figure out how many days away that is in the problem with that is that the birthdays that have already passed showed up with a negative birthday which might kind of confuse whoever's using the spreadsheet and so a great way to solve this is to use the IF function; we're going to edit that formula and say equal IF that calculation C2-TODAY is less than zero then we want to put nothing, so I'm going to push the quote to quotation marks to say that I don't want to see anything in the cell and then if that's not true then we go ahead and calculate the number C2-TODAY, close the today function, close the IF function, hit ENTER and when we copy that formula down to all of our cells you'll see that for any of the dates that have already passed is negative, but for the customers who still have birthdays yet to come those dates show up as positive, so a much better way to handle this situation.
Again thanks to Van for sending in that suggestion and we'll see you tomorrow for another netcast from MrExcel.
In yesterday's netcast we had a suggestion from Van on how to simplify the birthday suggestion and in the interest of time I simplified his suggestion and we created a formula yesterday that took a look at a date in column C –TPDAY, the today function to figure out how many days away that is in the problem with that is that the birthdays that have already passed showed up with a negative birthday which might kind of confuse whoever's using the spreadsheet and so a great way to solve this is to use the IF function; we're going to edit that formula and say equal IF that calculation C2-TODAY is less than zero then we want to put nothing, so I'm going to push the quote to quotation marks to say that I don't want to see anything in the cell and then if that's not true then we go ahead and calculate the number C2-TODAY, close the today function, close the IF function, hit ENTER and when we copy that formula down to all of our cells you'll see that for any of the dates that have already passed is negative, but for the customers who still have birthdays yet to come those dates show up as positive, so a much better way to handle this situation.
Again thanks to Van for sending in that suggestion and we'll see you tomorrow for another netcast from MrExcel.
