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.