We’ll also look at how to use the values in a table for data validation. This table contains a list of projects showing a sales pipeline. The last column is meant to show the current state of a given opportunity. To the right, in column G, is a list of possible stages. And if I venture to enter any value not on the list, Excel will display a warning. If I add a new row to the table, it also picks up the data validation....

Explanation Data validation rules are triggered when a user adds or changes a cell value. When C4 contains the text “See full list”, IF returns the named range long_list. When C4 is empty or contains any other value IF returns the named range short_list. If you are new to named ranges,this page provides a good overview. If they first select “vegetable” they then see a list of vegetables. More than one condition can be tested by nesting IF functions....

Each argument is provided as a number, and the result is aserial numberthat represents a valid Excel date. Apply a datenumber formatto display the output from the DATE function as a date. Note: tomove an existing dateforward or backward in time, see theEDATEandEOMONTH. TheTODAY functionreturns the current date to theYEAR function. The YEAR function extracts the year and returns the result to the DATE function as theyearargument. Themonthanddayarguments are hard-coded as 1....

Both dates are in January, so the formula is solved as follows and returns TRUE.

We don’t care at all about the day. The result is that each date in column D is tested against the date in cell B5. When the test returns TRUE, the IF function returns “x”. When the test returns FALSE, IF returns an empty string (""). This formula does the job and works fine. However, with a bit of creativity, we can streamline things quite a lot. Let’s see what we can do to shorten things up a bit....

WORKDAY automatically excludes weekends (Saturday and Sunday) and can optionally exclude holidays. WORKDAY accepts 3 arguments:start_date,days, and (optionally)holidays. It always returns the original start date, even if it is a weekend or holiday. Effectively, we are “tricking” WORKDAY into evaluating the original date as thestart_date. If the date is a workday, it will remain unchanged. Then we compare the original start date in cell B5 to the result of the WORKDAY function....

The result from DATEDIF is a number that corresponds to the time unit requested. The status of DATEDIF in Excel is somewhat mysterious. The desired interval is specified with theunitargument, which is supplied as text. The table below shows the available unit values and the results for each. Time units can be provided in upper or lower case (i.e. “ym” is equivalent to “YM”). This means DATEDIF will round months down even when it is very close to the next whole month....

The DATEVALUE function is meant to convert a date represented as atext stringinto avalid Excel date. The DATEVALUE function takes just one argument, calleddate_text. Ifdate_textis a cell address, the value of the cell must be text. Ifdate_textis entered directly into the formula it must be enclosed in quotes. A datenumber formatmust be applied to display this number as a date. Notice that Excel makes certain assumptions about missing day and year values....

For example, the pattern ? will match strings with 3 exactly characters in these more recent functions, but not in database functions. If you are using wildcards, test carefully. Multi-row criteria The criteria range for DAVERAGE can include more than one row below the headers. When a field is provided DCOUNT will only count numeric values in the field. UseDCOUNTAto count numbers or text values in a given field. Use the DMIN function to get the minimum value....

Note: in Excel’s date system,dates are serial numbers. January 1, 1900, is number 1 and later dates are larger numbers. To display date values in a human-readable date format, apply anumber formatof your choice.

Original data Data with helper columns

Both dates must bevalid Excel datesor text values that can be coerced to dates. The DAYS function only works with whole numbers, fractional time values that might bepart of a dateare ignored. If start and end dates are reversed, DAYS returns a negative number. The DAYS function returnsall daysbetween two dates, to calculateworking daysbetween dates, see theNETWORKDAYS function. For example: The DAYS function returns the number of daysbetweentwo dates. Working with nativeExcel dates(which are numbers) is a better approach....

Explanation The DAY function returns the day component of a date. The EOMONTH function returns the last day of the month for a given date. Use a positive value for months to move forward in time, and a negative number…