Conditional formatting---a specific date reminder

Today's sharing with you is how to format a cell containing date data that satisfies a condition by using conditional formatting. For example, the personnel department needs to give gifts to employees who have recently celebrated their birthdays, and a form for storing employee information is queried. (Of course, we may have a more advanced system, here is just an example.) This can be a birthday reminder, or a reminder of some other term, so how can you automatically mark the unit of the employee who has a birthday within a week when you open a worksheet that contains employee information. This requires a combination of conditions and formulas. Data such as.

650) this.width=650; "title=" capture.jpg "src=" Http://s3.51cto.com/wyfs02/M00/58/D5/wKioL1S-E0ehUtKIAAFd_ Gniij0722.jpg "alt=" Wkiol1s-e0ehutkiaafd_gniij0722.jpg "/>

I select A2:b16 and then click on the condition to select the new rule, as

650) this.width=650; "title=" capture1.jpg "src=" http://s3.51cto.com/wyfs02/M01/58/D5/ Wkiol1s-e5qtpqtvaaoeuvdjjpk920.jpg "alt=" Wkiol1s-e5qtpqtvaaoeuvdjjpk920.jpg "/>

In the Conditional Formatting dialog box, select Use a formula to determine wihch cells to format

Then enter =abs in the formula bar (DATE (Today ()), MONTH ($B 2), Day ($B 2))-today ()) <=7

Where year is equal to the age of the system, the value can be subtracted from the date on the day of the Open worksheet to give a value less than seven days, so let's think about it. Then month,day out the value of the month and day of the employee's birthday. Date then generates a date that is the same as the date year of the Open table and subtracts from the current date, less than 7 satisfies the condition and the cell is populated.

650) this.width=650; "title=" capture3.jpg "src=" http://s3.51cto.com/wyfs02/M00/58/D5/wKioL1S-FKqQu4fNAAGRVfCmH_ W934.jpg "alt=" Wkiol1s-fkqqu4fnaagrvfcmh_w934.jpg "/>

The results are as follows

650) this.width=650; "title=" capture5.jpg "src=" http://s3.51cto.com/wyfs02/M00/58/D5/ Wkiol1s-frazykhzaafsdwgzjvc243.jpg "alt=" Wkiol1s-frazykhzaafsdwgzjvc243.jpg "/>

You can apply this date period to any scenario

This article is from the "Guo Yong Technology blog" blog, make sure to keep this source http://guoyong.blog.51cto.com/408495/1606212

Conditional formatting---a specific date reminder