Date and Time in Excel Interesting facts: Excel97 and 2000 interpret two digit dates between 00 and 29 as 21st century dates. So, if you type in 1/1/23 meaning January 1, 1923 EXCEL will think you mean January 1, 2003. All dates between 30 and 99 are read as 20th century dates. Lotus 1-2-3 has a leap year bug that Microsoft kept in Excel to make their workbooks compatible with Lotus. It will only affect you if you use dates before March 1, 1900. By default, Excel can not calculate any dates before Jan 1 1900. All such dates are seen as text. You can only search for dates by using the whole date. For instance, if you spreadsheet says 6/29/69 you have to search using 6/29/1969. Excel can break down time into a unit as small as one-one thousandth of a second and as large as 10,000 hours. Excel converts dates and times to serial numbers in order to perform calculations. This only works when you have formatted the cells to be date and time. Source: Walkenbach, John. Microsoft Excel 2000 Formulas, pp. 140-146

The Date Function Use the date function to take numbers out of several cells and have them display as a date in one cell. 1. Click in the cell where you want your date to display (D2 for class). 2. Format that cell for DATE (right click on it, choose format cells, choose date) 3. 4. 5. 6.

Choose the insert function button from the toolbar Choose the DATE function (in the date and time category) Fill in the DATE function dialogue box with the appropriate cells. Click OK.

Serial number that represents the date The date that will display, if the cell is formatted for date.

Create a date series Use can use the AutoFill feature to sequentially add dates in a column. 1. Type in your initial date in the cell where you want to begin your series. a. You can also use a the function NOW if you want to start with the current date and time or the function TODAY is you want the current date. 2. Type 1/2/03 in cell F2 3. To fill in the next 7 cells in the column, simply click the box on the bottom right corner of the cell and drag down the next 7 cells.

Create a series of dates that are a week apart. 4. 5. 6. 7. 8. 9.

Go to cell G2 Type =Today() and hit enter or the green check. The current date should display. In cell G3 type =G2 + 7 and hit enter or the green check. You will now see the date for one week later. You can use AutoFill to fill in more dates, all one week apart.

Create a series of dates that are a month apart. To create a series of dates that are a month apart we will nest the YEAR, MONTH and DAY functions into the DATE function. 10. Go to cell H2. 11. Type in the formula =DATE(YEAR(G2), MONTH(G2) + 1, DAY(G2)) This formula takes the year from the date in cell G2 (TODAY), the month from the cell in G2 plus 1 (to go to the next month), and the Day from cell G2 and puts them in the date function to display one date in cell H2. 12. You can use AutoFill to complete the series. Let’s see what happens when you use the last day of the month. 13. Change cell G2 to 6/30/03. Notice that H2 goes to 7/30/03 (not the last day of July) 14. Change cell G2 to 8/31/03. Notice that H2 goes to 10/1/03 (not 9/30/03). Create a series of weekdays only (no weekends) 15. First, make sure that you start with a weekday date (cell J2 for class)

2

16. In J3 enter the formula =IF(WEEKDAY(J2)=6, J2+3,J2+1)) 17. Use Auto Fill to complete the series. Notice that the weekend days are skipped. Calculating the number of days between two dates You can use simple subtraction to do determine the number of days between two dates. 1. Go to cell A11 and enter the formula =C13-B13 2. In cell B11 enter the formula =B13-C13 3. Notice that if the second date is more recent that the first date, the result will be negative. If you always want to see a positive number you can use the Absolute Value function. That would make your formula =ABS(B13-C13) Sometimes you may want to calculate the number of work days between two dates. In other words, you want to know how many days are between two dates, not counting the weekends or holidays. The NETWORKDAYS function is your answer. You can only use this function if you have installed the Analysis Tool Pak. To install the Analysis ToolPak 4. On the Tools menu, click Add-Ins. 5. If Analysis ToolPak is not listed in the Add-Ins dialog box, request it from IS. 6. Select the Analysis ToolPak check box. To use the Analysis ToolPak 7. On the Tools menu, click Data Analysis. 8. In the Analysis Tools box, click the tool you want to use. 9. Enter the input range and the output range, and then select the options you want. Note Add-ins you select in the Add-Ins dialog box remain active until you remove them. How to remove an add-in. Excel does not know which days are holidays, so I have created a range on another sheet called Holidays and PTO. 10. In cell D13 click on the functions button. 11. Under Date and Time functions, choose NETWORKDAYS 12. Complete the dialog box, as seen here.

3

The WORKDAY function is the opposite of NETWORK days. If you have a start day, and the number of days you have to complete a project, this function will tell you the due date. WORKDAY counts weekdays, Monday thru Friday, as a working day. WORKDAY will also accept the third argument, a range representing a list of holidays. 13. In cell E13 click on the functions button. 14. Under Date and Time choose WORKDAY 15. Complete the dialog box as seen here. You answer should be the same date as seen in cell C13.

Calculating a Person’s Age In general, you can determine the number of years between two dates by using subtraction. The formula would be =Year(A1)-Year(B1), when A1 and B1 both contain dates. However, this does not work to determine an age, since we can say our age in parts of a year. 1. In cell A18, enter your birthday. 2. In cell B18, enter the formula =YEARFRAC(TODAY(), A18,1)) If you want to see the age as a whole number, use =INT(YEARFRAC(TODAY(),A1,1))

4

Enter a Time Stamp into a Cell Go to the sheet named Time. Enter a Time Stamp into a Cell Crtl + Shift+: Try it in cell A1 Summing Times Greater than 24 Hours Look at the table in cells in A2:B10. Notice that the sum of 18:30 is incorrect—it should be 42:30. Excel assumes a 24 hour day, so you need to do a custom format in cell B10. 1. Right click on B10 and choose Format cells 2. Choose custom (bottom of the list) 3. Scroll down until you see the item with brackets around h 4. Click OK

Converting from Military Time If you have a group of cells in military time that you want to convert to standard time, use the TIMEVALUE function. We have such a list in D2:D7, which is formatted as General. 1. Begin in cell E2, which has already been formatted for standard time. 2. Enter TIMEVALUE(LEFT(D3,2)&”:”&RIGHT(D3,2)) [D3 is the cell that contains the military time, so will change based on your worksheet. 2 will always be 2] 3. Enter the formula (or use the green check) Note: If you have a cell formatted for standard time and you enter a military time, Excel will display the standard time. Convert Decimal Hours, Minutes, or Seconds to a time Simply divide by the number of hours (24), minutes (1440) or seconds (86400) in a day. 1. 2. 3. 4. 5. 6.

In cell B14 enter the formula =A14/24 This changes 10.8 hours into 10 hours and 48 minutes In cell E14 enter the formula =D14/1440 This changes 90 minutes to 1 hour and 30 minutes In cell H14 enter the formula =G14/86400 This changes 15555 seconds into 4 hours 19 minutes and 15 seconds.

5