Date and Time in Excel
Creating formulas that use date and time in excel can be a little confusing if you don’t have a good understanding of how Excel treats these types of values. After you’re equipped with this understanding, you can begin to make good use of the many Date and Time functions that the program offers.
This chapter begins with a quick overview of date and time in Excel and how you can use them to build simple formulas that calculate differences between elapsed date and time in excel. The tutorial goes on to survey Excel built-in Date and Time functions, including the Date functions that are available after you’ve installed and activated the Analysis ToolPak add-in.
Understanding Date And Time In Excel
Excel doesn’t treat the dates and times that you enter in the cells of your worksheet as simple text entries. Any entry with a format that resembles one of the date and time number formats utilized by Excel is automatically converted, behind the scenes, into a serial number. In the case of dates, this serial number represents the number of days that have elapsed since the beginning of the 20th century so that January 1, 1900, is serial number 1; January 2, 1900, is serial number 2; and so forth. In the case of times, this serial number is a fraction that represents the number of hours, minutes, and seconds that have elapsed since midnight, which is serial number 0.00000000, so that 12:00:00 p.m. (noon) is serial number 0.50000000; 11:00:00 p.m. is 0.95833333; and so forth.
Changing the Regional Date Settings
Excel isn’t set up to automatically recognize European date formats in which the number of the day precedes the number of the month and year.
For example, you may want 6/11/2019 to represent November 6, 2019, rather than June 11, 2019. If you’re working with a spreadsheet that uses this type of European date system, you have to customize the Windows 10 Regional settings for the United States so that the Short Date format in Windows programs, such as Excel and Word 2019, use the D/m/yyyy (day, month, year) format rather than the default M/d/yyyy (month, day, year)
To make this change, you follow these steps:
1. Click the Windows 10 Start button and then click the Settings button on the Start menu. Windows 10 opens the Windows Settings dialog box.
2. Click the Time & Language button on the Windows Settings screen.
The Date and Time settings appear in the Settings screen.
3. Click the Change Date and Time formats link that appears under the Format examples that show you the current long and short date and time formatting.
The Change Date and Time Settings screen displays drop-down text boxes where you can select new formatting for the short and long dates.
4. Click the Short Date drop-down button, click the dd-MMM-yy format at the bottom of the drop-down list, and then click the Close button.
After changing the Short Date format in the Change Date and Time Formats Settings screen. the next time you launch Excel 2019, it automatically formats dates, for example, 3/5/19 is interpreted as May 3, 2019, rather than March 5, 2019.
Excel Formula to Calculate Time Between Two Dates
Most of the date and time in excel formulas that you build are designed to calculate the number of days or years that have elapsed between two dates. To do this, you build a simple formula that subtracts the earlier date from the later date.
For example, if you input the date 4/25/95 in cell B4 and 6/3/14 in cell C4 and you want to calculate the number of days that have elapsed between April 25, 1995, and June 3, 2014, in cell D4, you would enter the following subtraction formula in that cell:
Excel then inputs 6979 as the number of days between these dates in cell
D5 using the General number format.
Some spreadsheets require that formulas calculate the amount of elapsed the time between a starting and an ending time. For example, suppose that you keep a worksheet that records the starting and stopping times for your hourly employees and you need to calculate the number of hours and minutes that elapse between these two times in order to figure their daily and monthly wages.
To build a formula that calculates how much time has elapsed between two different times of the day, subtract the starting time of day from the ending time of day. For example, suppose that you enter a person’s starting time in cell B6 and ending time in C6. In cell D6, you would enter the following subtraction formula:
Excel then returns the difference in cell D6 as a decimal value representing what fraction that difference represents of an entire day (that is, a 24-hour period). If, for example, cell B6 contains a starting time of 9:15 a.m. and cell C6 contains an ending time of 3:45 p.m., Excel returns the following decimal value to cell D6:
To convert this time of day into its equivalent decimal number, you convert the time format automatically given to it to the General format (Ctrl+Shift+`), which displays the following result in cell D6:
To convert this decimal number representing the fraction of an entire day into the number of hours that have elapsed, you simply multiply this result by 24 as in =D6*24, which gives you a result of 6.5 hours.