Purpose
Return value
Syntax
=XOR(logical1,[logical2],...)
- logical1 - An expression, constant, or reference that evaluates to TRUE or FALSE.
- logical2 - [optional] An expression, constant, or reference that evaluates to TRUE or FALSE.
Using the XOR function
The XOR function performs what is called “exclusive OR”, in contrast to the “inclusive OR” performed by the OR function . Whereas the OR function returns true if any input is TRUE, XOR only returns TRUE in specific cases. In the simplest case, with just two logical statements, XOR returns TRUE only if one of the logicals is TRUE. If both values are TRUE, XOR returns FALSE.
The concept of exclusive OR is more common in the world of programming. In plain English, you can think of a sentence like this: “I’m either going to visit New York or San Francisco this summer”. Nothing prevents the speaker from visiting both, but the meaning is clearly that they plan to visit only one or the other. If they visit one or the other, the original statement is TRUE. If they visit neither or both, the original statement is FALSE.
Example #1 - two values
In the example shown, the formula in D5, copied down, is:
=XOR(B5,C5)
At each row, XOR only returns TRUE when B5 and C5 contain a single TRUE or equivalent value.
Example #2 - more than two values
With more than 2 values, the behavior of XOR is a bit different. With three or more logicals, XOR only returns TRUE when the number of TRUE values is odd, as shown in the following example:
In this example, XOR is given a range with five values in a single argument instead of five separate arguments. The formula in G6 copied down is:
=XOR(B6:F6)
The result is TRUE only when the number of TRUE values columns B through F is an odd number.
Notes
- Logical arguments must evaluate to TRUE or FALSE, 1 or 0, or be references that contain logical values.
- XOR returns #VALUE! if no logical values are found.
- With more than two logicals, XOR returns TRUE when the number of TRUE logicals is odd, and FALSE if not.
- XOR was introduced in Excel 2013.
Purpose
Return value
Syntax
=DATE(year,month,day)
- year - Number for year.
- month - Number for month.
- day - Number for day.
Using the DATE function
The DATE function creates a date using individual year, month, and day arguments . Each argument is provided as a number, and the result is a serial number that represents a valid Excel date. Apply a date number format to display the output from the DATE function as a date.
In general, the DATE function is the safest way to create a date in an Excel formula, because year, month, and day values are numeric and unambiguous, in contrast to text representations of dates which can be misinterpreted.
Note: to move an existing date forward or backward in time, see the EDATE and EOMONTH .
Example #1 - hard-coded numbers
For example, you can use the DATE function to create the dates January 1, 1999, and June 1, 2010, with the following syntax:
=DATE(1999,1,1) // returns Jan 1, 1999
=DATE(2010,6,1) // returns Jun 1, 2010
Example #2 - cell reference
The DATE function is useful for assembling dates that need to change dynamically based on other inputs in a worksheet. For example, with 2018 in cell A1, the formula below returns the date April 15, 2018:
=DATE(A1,4,15) // Apr 15, 2018
If A1 is then changed to 2019, the DATE function will return a date for April 15, 2019.
Example #3 - with SUMIFS, COUNTIFS
The DATE function can be used to supply dates as inputs to other functions like SUMIFS or COUNTIFS , since you can easily assemble a date using year, month, and day values that come from a cell reference or formula result. For example, to count dates greater than January 1, 2019, in a worksheet where A1, B1, and C1 contain year, month, and day values (respectively), you can use a formula like this:
=COUNTIF(range,">"&DATE(A1,B1,C1))
The result of COUNTIF will update dynamically when A1, B1, or C1 are changed.
Example #4 - the first day of the current year
To return the first day of the current year, you can use the DATE function like this:
=DATE(YEAR(TODAY()),1,1) // first of year
This is an example of nesting . The TODAY function returns the current date to the YEAR function . The YEAR function extracts the year and returns the result to the DATE function as the year argument. The month and day arguments are hard-coded as 1. The result is the first day of the current year, a date like “January 1, 2021”.
Note: the DATE function actually returns a serial number and not a formatted date. 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 the number format of your choice.
Notes
- The DATE function returns a serial number that corresponds to an Excel date.
- Excel dates begin in the year 1900. If the year is between zero and 1900, Excel will add 1900.
- The DATE function accepts numeric input only and will return #VALUE if given text.
