Image: Rob Schultz
Last week I explained how Excel uses its own, unique system of serial numbers to calculate dates and times. That piece covered nine Date & Time functions: NETWORKDAYS(), DATEVALUE(), NOW(), DAY(), MONTH(), YEAR(), HOUR(), MINUTE(), SECOND(), plus the basic SUM function for adding and subtracting start dates and end dates.
This week I’ll explain how to use seven more Date & Time functions: DATE(), DAYS(), DAYS360(), TODAY(), TIME(), TIMEVALUE(), and WEEKDAY().
DATE()
Go to cell A5. From Formulas > Function, select Date & Time. In the drop-down list, select DATE.
JD SartainIn the Functions Arguments dialog window, enter a year, month, and day. Notice the Formula result in the bottom left corner of this dialog (also shown at center right). This is the serial equivalent of the date you entered. Click OK. If the serial number is not shown in cell A5, go to Home > Number and select the General format.
JD SartainDAYS()
In addition to using the basic SUM function to determine the number of days between two dates, you can also use the DAYS function. Go to cell A3. From Formulas > Function, select Date & Time. In the drop-down list, select DAYS.
The Functions Arguments dialog window appears. In the End_Date field box, enter the day, month, and year of the end date inside double quotes; e.g., “5/29/2016” using slashes or dashes. In the Start_Date field box, enter the day, month, and year of the start date inside double quotes; e.g., “2/11/1992” using slashes or dashes. Notice the Formula result in the bottom left corner of this dialog (also shown at center right). Click OK.
You can also “point” to the dates. For example, enter the End_Date in A4 and the Start_Date in A5. From Formulas > Function, select Date & Time, then select DAYS from the drop list. In the End_Date field box, point to A4, and in the Start_Date field box, point to A5. Or just move your cursor to A7 and type: =DAYS(A4, A5).
JD SartainDAYS360()
Use the same instructions above for DAYS() function to perform this DAYS360() function. The only difference between these two formulas is the length of the calendar year. The first (above) is based on a normal 365-day calendar year, with 12 months of varying days. The second is based on a 360-day calendar year with 12 30-day months. Who uses a 360-day calendar? Some accounting offices are set up on 360-day years.
TODAY()
Last week’s article explained how the NOW() function works—that is, it calculates the serial number of today’s date, including the time. To see the actual date and time, you must then choose a Date & Time format. The TODAY() function is exactly the same except without the time, and it displays the results already in a date format.
So if you enter =TODAY() in cell A9, Excel returns today’s date. You can also go to Formulas > Function, select Date & Time, then click TODAY from the drop-down list. Notice the Function Arguments dialog box says: Returns the current date formatted as a date. This function takes no arguments. That means you don’t have to do anything except click OK.
JD SartainTIME()
Go to cell A11. From Formulas > Function, Select Date & Time. In the drop-down list, select TIME. In the Functions Arguments dialog box, enter an hour, minute, and second. Notice the Formula result in the bottom left corner of this dialog (also shown at center right). This is the serial equivalent of the time you entered. Click OK. To convert the serial number to a Time format (as shown in cell A13), go to Home > Number and select the Time format.
JD SartainTIMEVALUE()
The TIMEVALUE() function is similar to the DATEVALUE() function except it’s time instead of a date. Dates and times are frequently copied into Excel as text. Importing data generally produces more accurate results; however, sometimes even the imported data comes in as text. For example, dates copied or imported from an ASCII file, database, or even a text file is read by the system as text.
Use the TIMEVALUE() function to convert the text times to Excel serial numbers. Move your cursor to cell A14. Enter a time in text format as if it were imported from an ASCII file (insert an apostrophe in front of whatever time you enter to make Excel treat it as text). Move the cursor to A15. From the Formulas tab, select Date & Time > TIMEVALUE. The Function Arguments window will appear. In its Time_text field box, point (click) the text-time in cell A14, and the Excel serial number appears in cell A15.
JD SartainNow, format the cell to a Time format (times moved to D14 and D15 for a better screenshot). Highlight D15. From Home > Number, click the tiny arrow in the bottom right corner of this group, and the Format Cells window opens. Select Time from the Category (left panel). Scroll through the time options in the Type panel to choose a better format. Unlike the text date in cell D14, you can now use formulas to calculate times.
JD SartainWEEKDAY()
The WEEKDAY() function provides the day of the week as a number. The default numbers for these days are 1-7, or Sunday through Saturday, respectively. The syntax (that is, the arrangement/structure of a formula or command) for the WEEKDAY function is: WEEKDAY(Serial_Number,Return_Type); one comma between arguments and no spaces.
The Serial Number represents the date or refers to a cell with a date or serial number. The Return_Type (which is an optional argument) represents the day-of-the-week option you prefer to use:
1 or (nothing) is 1 through 7 = Sunday thru Saturday
2 is 1 through 7 = Monday thru Sunday
3 is 0 through 6 = Monday thru Sunday
11 is 1 through 7 = Monday thru Sunday
12 is 1 through 7 = Tuesday thru Monday
13 is 1 through 7 = Wednesday thru Tuesday
14 is 1 through 7 = Thursday thru Wednesday
15 is 1 through 7 = Friday thru Thursday
16 is 1 through 7 = Saturday thru Friday
17 is 1 through 7 = Sunday thru Saturday
NOTE: Return Number 2 and Return Number 11 are the same, because Return Numbers 11 through 17 were added later (in Excel 2010) and do not work in previous versions of Excel.
JD SartainThese functions are useful if you need to use formulas to calculate dates, or if you have a long column of dates and you want to know which days are weekdays and which are weekend days. For example: enter 10 random dates in cells A3 through A12. Move to C3 and enter the following WEEKDAY() formula: =IF(WEEKDAY(A3,2)<6, “workday”,”WEEKEND”). In other words: Use the WEEKDAY function and point to cell A3 with a Return Type 2 (for 1 thru 7 = Monday thru Sunday) and IF it’s less than 6, than it’s a workday; otherwise, it’s a weekend.
Copy the formula from cell A3 down to cell A12 and see which days are weekends and/or weekdays.
JD SartainThere are many other formulas that use the WEEKDAY() function. Check back next week for those options plus eight more Date & Time functions.
