Sometimes, you would like to compute certain information or show some results only for the dates till date. In other words, you would like to hide the results for all the future dates. In this article, you’ll learn how to do this in Excel. As the logic behind this would be to hide the results based on certain conditions (in this case date), we’ll be using conditional formulas like IF, COUNTIF and SUMIF.
Let’s take an example and see how we can accomplish this. In this example, we are considering Daily forecast vs actual orders comparison table. For sake of simplicity, I’ve taken data only for one month. Please use the following Excel file as the initial data to start with:
Follow the below steps to Hide Formula results based on Date:
- When you download and open the file, you will see the data like the snapshot below
- Notice that the Actual orders data is filled only till 12th Jul, this is because this file was created on 13th Jul and the data for orders was available only till 12th Jul
- You can make the changes to the date as per the date you are in just by updating date in cell A2 to the first day of your current month
- First, let’s have a look at how we can calculate the Actual to Forecast Ratio without any conditions involved.
- As shown in the screenshot above we use the formula C2/B2 in cell B2 to get the ratio.
- But, if we drag the same formula down to all the other rows, you’ll notice the result will be 0 from cell D14 onwards (as highlighted in the screenshot below)
- This is not desirable, instead, we would like to hide these results
- Now, let’s see how we can calculate the Actual to Forecast Ratio only till 12th based on the entry made in column C
- Let’s update the formula in cell D2 to the following conditional formula using an If condition:
- The IF statement takes three parameters. First, is the logical condition to check. Second, the value or the result to show if the condition is evaluated to be TRUE. Third, the value or the result to show if the condition is evaluated to be FALSE
- In this case, we are checking if the value in C2 is blank or not. If the condition evaluates to be TRUE (i.e. C2 is blank) then make the result of the formula blank. If the condition evaluates to be FALSE (i.e. C2 is not blank) then compute the result as C2/B2
- If we drag the same formula down to all the rows below, we’ll notice that the results populate only till the 12th Jul row (as highlighted in the screenshot below)
- Now, to make the condition dependent on date, we’ll use the date comparison in the IF condition then, evaluate the results accordingly.
- We’ll compare the date in column A with the current date using TODAY() formula and based on that show a blank text or evaluate the ratio.
- If the date is today or later, in that case, show blank else calculate the ratio, to perform this we’ll use the following formula:
- Note that the result of this formula is same as earlier (refer to the screenshot below-taken on 13th July)
With this, I would like to conclude by saying with the help of conditional statements like IF we can modify the results of formula based on dates and other parameters like blank cells. If you would like to have a look at the solution refer to the following file:
Bonus: If You’d like to learn about how Date and Time work in Excel + All Excel Date & Time functions, you can watch our Excel Date & Time Masterclass on Youtube!
You can also download our Excel Date & Time functions Cheat sheet using the form below!
As alway, please leave a comment below if you have any queries/comment, we are always glad to read your feedback!
Did you find this useful? Why not share the love and tell your friends on the different social media platforms.
- Learn all about the new XLOOKUP Function in Excel! - September 27, 2019
- FOR EACH Loop in Excel VBA - December 15, 2018