Date Validation

 If you have 90 days to settle an account, and would like to know if a given date is within 90 days of today’s date, you can use a combination of the TODAY() date function and the IF() function. This will enable you to check if a given date is within 90 days of, well, today.

By using a combination of the TODAY() date function and the IF() function one is able to check if a given date is within 90 days of today’s date.  The TODAY() function returns the date as per the system date of the machine, using the YYY/MM/DD  format for the date.  Once in a cell, this date can be used in calculations. The example in this tip  starts with the following information in Excel: a date listed in cell A1, with the cell formatted to represent a date in DD-MMMM-YY format. 

Applies To: MS Excel 2003, 2007 and 2010

 tip1

1. Select cell B2

2. Type in =TODAY() and press Enter.  Use the Format Painter in the Home ribbon to format the date.

tip2 

3. Select cell C1. Type in =B1-A1  and press Enter

4. Select cell C2. Type in =(TODAY()-A1)>=90 and press Enter. This will return a FALSE value, as the result (20) is not greater than or equal to 90

tip3

5. With cell C2 still selected, type in =IF(TODAY()-A1>=90,”Yes”,”No”) and press Enter

 tip4

The formula will subtract the entered date from today‟s date, then compare the value against 90. If the value is greater than or equal 90, the result will be “Yes”, otherwise “No”.

 

If you have any suggestions for an Excel Tips & Tricks topic, or you would like help with a particular function, please email enablement@alchemex.com and yours could be the next Tip of the Week.

Alchemex provides examples of MS Excel procedures for illustration only, without warranty expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The MS Excel procedures on this web site are provided “as is” cannot be guaranteed that they can be used in all situations