Explanation
In Excel, dates are simply serial numbers. In the standard date system for windows, based on the year 1900, where January 1, 1900 is the number 1. Dates are valid through 9999, which is serial number 2,958,465. This means that January 1, 2050 is the serial number 54,789.
In the example, the date is March 9, 2016, which is the serial number 42,438. So:
= B4-TODAY()
= January 1 2050 - April 27, 2014
= 54,789 - 42,438
= 12,351
This means there are 13,033 days before January 1, 2050, when counting from March 9, 2016.
Without TODAY
Note: you don’t need to use the TODAY function. In the second example, the formula in D6 is:
=B6-C6
Concatenating with text
In the third example, the same basic formula is used along with concatenation operator (&) to embed the calculated days in a simple text message:
="Just "& B6-C6 &" days left!"
Since there are 15 days between December 10, 2014 and December 25, 2014, the result is this message: Just 15 days left!
Workdays only
To calculate workdays between dates, you can use the NETWORKDAYS function as explained here.
Explanation
Dates in Excel are serial numbers that start on 1/1/1900, which is represented by the number 1. In the example shown, the formula in cell D6 simply subtracts the numeric value of 1/1/1999 (36161) from the numeric value of 1/1/2000 (36526) to get a result of 365. The steps look like this:
=C6-B6
=36161-36526
=365
Working with today
To count the number of days between an earlier date and today, you can use the TODAY function :
=TODAY()-earlier_date
To calculate the number of days between a later date and today, use:
=later_date-TODAY()
Note that TODAY will recalculate on an ongoing basis. If you open the workbook at a later date, the value used for TODAY will update and you will see a new result.
The DAYS function
The DAYS function calculates the number of days between two dates using a start date and an end date. With a start date in A1 and end date in A2:
=DAYS(A2,A1)
Will return the same result as:
=A2-A1
Both dates must be valid Excel dates or text values that can be coerced to dates. If start and end dates are reversed, DAYS will return a negative number.
Workdays between dates
The formulas above count all days between two dates, to calculate working days between dates, see the NETWORKDAYS function or NETWORKDAYS.INTL function . See this article for more details.
