Date and date functions in Excel 2016

In this chapter from Microsoft Excel Data Analysis and Business Modeling, 5th Edition, author Wayne Winston explores the most commonly used month-day-year formats in Microsoft Excel 2016.

Questions answered in this chapter:

  • When I enter dates in Excel, I often see a number such as 37625 rather than a date such as 1/4/2003. What does this number mean, and how do I change it to a normal date?
  • Can I use a formula to automatically display today’s date?
  • How do I determine a date that is 50 workdays after another date? What if I want to exclude holidays?
  • How do I determine the number of workdays between two dates?
  • I have 500 different dates entered in an Excel worksheet. How do I write formulas to extract from each date the month, year, day of the month, and day of the week?
  • I am given the year, month, and day of the month for a date. Is there an easy way to recover the actual date?
  • My business has purchased and sold machines. For some, I have the date the machine was purchased and the date the machine was sold. Can I easily determine how many months we kept these machines?
  • How can I place a static (unchanging) date in a worksheet?

To illustrate the most commonly used month-day-year formats in Microsoft Excel 2016, let’s suppose today is January 4, 2004. You can enter this date as any of the following:

  • 1/4/2004
  • 4-Jan-2004
  • January 4, 2004
  • 1/4/04

If you use only two digits to represent a year, and the digits are 30 or higher, Excel assumes the digits represent years in the twentieth century; if the digits are lower than 30, Excel assumes they represent years in the twenty-first century. For example, 1/1/29 is treated as January 1, 2029, but 1/1/30 is treated as January 1, 1930. Each year, the year treated as dates in the twenty-first century increases by one.

Read more about dates and date functions here.

Skip to main content