Image: Rob Schultz
If you need to calculate dates in your spreadsheets, Excel uses its own unique system, which it calls Serial Numbers. Every date (month, day, year) and time (hour, minute, and second) has an equivalent Serial Number dating back to January 1, 1900 at 00:00:00 (hours, minutes, seconds). Not to be confused with Julian dates, which mainframes and many programming languages use, Excel serial numbers are unique to Microsoft products.
Open Excel and enter the numbers 1 through 5 in cells A3 through A7. Highlight the range, then from the Home tab’s Number group, select Short Date from the drop-down list. Notice that your numbers 1 through 5 have changed to the following dates: 1/1/1900 through 1/5/1900.
JD SartainNow enter the dates in cells B3 through B7 for the first five days in May; 5/1/2016 through 5/5/2016. Highlight the range; then from the Home tab’s Number group, select General from the drop-down list. Notice that your five dates have changed to the following numbers: 42491 through 42495.
These are the numeric equivalents of the first five days in May of 2016. Excel calls them serial numbers and uses them to calculate dates and times.
Next, enter the following formula in column C to convert Excel’s serial-number dates to Julian dates:
=RIGHT(YEAR(B1),2)& B1-DATE(YEAR(B1),1,0). This is a useful formula if you have to export your spreadsheet data with dates to another program such as a database or timestamp application.
This formula converts Julian dates back to serial dates: =DATE(1900+INT(Julian_Date/1000),1,MOD(Julian_Date,1000))
Number of days between two dates
Magnum P.I. charges $200 a day plus expenses for his services as a private investigator. He wants to know how much money he’ll earn if he works every day from February 21, 2016 through May 1, 2016. Instead of manually calculating this timeframe (accounting for number of days per month, Leap Year, etc.), you can use Excel to quickly solve this problem.
The formula for this calculation is simple: Just subtract the end date from the start date:
=SUM(B1-A1). The answer is 70 days. Now add Magnum’s daily rate to the end of this formula:
=SUM(B1-A1)*200 and you get the answer in one easy formula: $14,000.00. That works out to about $25 an hour—lousy wages even for the 1980s.
JD SartainNow Magnum wants to know how much he’ll earn if he works the same dates, but takes weekends off. Enter this formula in C3: =NETWORKDAYS(A3,B3). The new answer is 50 days. Or select Date & Time from the Formulas tab, click NETWORKDAYS, and the Function Arguments window opens. Enter the start date and end date in the field boxes or click the corresponding cells (point to them) on the spreadsheet, then click OK. Same answer: 50 days. Now add Magnum’s daily rate to the end of this formula: =NETWORKDAYS(A3,B3)*200 and you get the new salary in one basic step: $10,000.00.
JD SartainConvert text dates to actual dates
When dates are copied into Excel, they’re often copied as text. Importing dates is a better option but, sometimes, even imported dates come in as text. When this happens, use the DATEVALUE function to convert the text dates to Excel serial numbers. To test this function, enter five dates—as text—in cells A5 through A9. Insert an apostrophe before each date, and that will convert the date to a text entry.
NOTE: When dates are copied or imported into Excel as text fields (from an ASCII file, database, or even a text file), the system automatically reads them as text; however, if you copy a date from MS Word (using copy/paste/text), Excel recognizes it as a date.
Move your cursor to B5. From the Formulas tab, select Date & Time > DATEVALUE. The Function Arguments screen appears. You do not have to re-enter the date in the Date_text field box. Just click the text-date in cell A3, and the Excel serial number appears in cell B5. Copy the formula down to B9.
JD SartainNow, all you have to do is format the cells to whichever date format you prefer. Highlight cells B5 through B9. From the Home tab’s Number group, select a number format from the General drop-down list. You may also click the tiny arrow in the bottom right corner of this group, and the Format Cells window opens. Select Date from the Category (left panel). Scroll through the date options in the Type panel, and choose the one that fits your project. Now your Excel serial numbers are displayed as actual dates, which means you can use formulas to make calculations.
JD SartainSerial numbers for all date formats
All businesses use databases and spreadsheets to calculate dates and times daily for payroll, expenses, inventory, sales reports, accounting, and more.
JD SartainExcel provides serial numbers for all of the date and time formats, from months down to individual seconds. See for yourself: Place your cursor in cell A1. Go to Formulas > Functions and select Date & Time > NOW. Then click OK in the popup window.
From the Home tab’s Cells group, select Format > Format Cells. In the Format Cells window, select a custom date format with minutes and seconds, then click OK.
Next, convert the date to an Excel serial number (as we did above, select the General format from the Home tab’s Number group).
The following formulas convert the serial number to the weekday, month, year, hour, minutes, and seconds. Here’s how.
Go to Formulas > Functions Library and select Date & Time > DAY. When the Function Arguments window opens, point to cell A1 or enter A1 in the Serial Number field box. Excel then displays just the day of the month—in this case, the 9th.
JD SartainSelect the following formulas from the Formulas tab, Functions Library group, Date & Time for cells B2 through B6: MONTH, YEAR, HOUR, MINUTE, SECOND, or enter the following formulas:
=MONTH(A1) returns today’s month
=YEAR(A1) returns today’s year
=HOUR(A1) returns today’s hour
=MINUTE(A1) returns today’s minute
=SECOND(A1) returns today’s second
JD SartainAll of these formulas can be used separately or with other Date & Time functions to calculate everything from days per quarter; to how many weeks or days between now and Christmas; to how many hours in the month of February on a Leap Year.
Excel Windows vs. Excel Mac
Windows Excel uses the 1900 date system (a carry-over from Lotus 1-2-3), while Excel for the Apple Mac uses the 1904 date system. So the serial number 1 in Windows Excel equals 1/1/1900, 12:00 am, while in Mac Excel it’s 1/2/1904, 12:00 am. Seems like Microsoft should have fixed this by now, but it’s still a problem. Transferring files between the two programs should work because the date system is stored in each file. But copy-and-paste is a different story. Different date systems on different platforms will result in incorrect dates, which will be off by four years and one day.
To make the Apple Mac’s Excel 1904 date system compatible with the Windows Excel 1900 date system:
JD SartainFrom the File tab’s Backstage menu, select Options > Advanced. Scroll down the window until you see When Calculating this Workbook (the 10th subhead down the page). Check the box that says: Use 1904 Date System, then click OK.
There are a dozen (or so) more of these type of functions in Excel. Stay tuned for Part Two, which covers the remaining Date & Time formulas with examples.
