It’s a Date: Excel Meets the Calendar

There are some powerful math features in Excel that can help you with your date data. First, let’s take a look at how Excel interprets dates.

Although you are seeing a date formatted properly, behind it there is just a number. These are referred to as serial number dates. Each date beginning with January 1, 1900 (date number 1) has its own number. So, for example, if the date is 3/4/2013, the serial number is 41337, or the 41,337th day since 1/1/1900. Knowing that makes it easy to understand that when we are adding or subtracting dates, we are just adding and subtracting numbers, so 41337 + 90 is 41427 or 6/2/2013.

There are actually functions that work with dates, too. Here are a few very useful ones:

• TODAY() Today’s date as understood by your computer
• NOW() The date and time, right now.
• NETWORKDAYS(start_date,end_date,holidays)

Let’s have a look at NETWORKDAYS. Let’s say you have project start and end dates. The true number of workdays for our example are weekdays Monday through Friday less holidays that fall on those days. If we have our project’s start and end dates in columns A and B, respectively, we can calculate the number of workdays in C with NETWORKDAYS. Before we type in the formula, let’s put our holidays in a list in another worksheet and assign the name Holidays to it using the Name Box.

The formula we’ll type in column C is:

=NETWORKDAYS(A1,B1,holidays)

Now, you have the actual number of workdays to determine how resources can be allocated, tasks assigned and man-hours allocated.