Now, the cells which contain completed will appear in red. In the Format Cell, click on Fill under that, select red color, and click OK. Similarly, under New Formatting Rule, select “Format only cells that contain”, under the cell values, select “equal to,” and enter the keyword “Pending”, click on the format option for color selection. Now the cells which contain completed will appear in green color.Īfter selecting a color, the output is shown below. In the format cell, click on Fill under that, select Green color, and click OK. Under the New Formatting Rule, select “Format only cells that contain” under the cell values, select equal to enter the keyword “Completed”, and click on the format option for color selection. Now, select the entire date range & apply conditional formatting to the date range by applying a new rule. Make the text alignment of the date range to the center and remove the gridlines & table borders. So that it gives status reference values if the IF logical condition criteria meet. Now, apply this formula to a whole date range, i.e., By selecting the entire date range, you need to press CTRL+R & CTRL+D keys. Similarly, I will be dragging this formula down I need to lock row reference, i.e., 6 th row (G$6 & G$6). To get an output, I need to lock column C & D (i.e., $C7 & $D7) & the status reference column as well, i.e., column E (“$E7”). I apply this formula towards other columns on the right-hand side (Other date range). If I want to apply this formula to a whole date or project range, then I need to modify the formula with mixed reference, i.e., row & column reference modification. =IF(AND(Jan 1 st, 2019>= Jan 1 st, 2019, Jan 1 st, 2019<=Jan 2 nd2019), If yes or if the criteria meet, give the value of status Cell reference or leave it blank). Illustration #3 – Formula Explanation (Two Conditions) Set the column width as 4, and the row height is adjusted for a better text content appearance. Now, enter the alignment to a 90-degree orientation & format it in a similar way by dragging that task 1 start date to the last task end date or until you get Jan 30 th, 2019. Go to Home > Format > Select Format Cells option.Īfter selecting the Format Cells option, the Format Cells window appears. Illustration #2 – Project Timeline Creation After using the formula, the output is shown below.Īpply the MAX formula in cell “D4” to get the maximum value under the project End Date range and enter the total project duration.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |