This is part four of the Excel Tutorial Series. Read all tutorial posts by browsing the Tutorial category.
There are several important functions for working with dates and times in Excel: NETWORKDAYS and WORKDAY, INT and MOD. The first two require a reference to the Analysis ToolPak. If you use Excel 2007, you already have these functions baked in, but for the rest of us, go to Tools » Add-Ins and check off Analysis ToolPak. If you don't see Analysis ToolPak listed, it may not be installed. You'll need to install it from your Office CD.
(Note: Descriptions of each function were taken from: Excel Function Reference)
NETWORKDAYS
If you need to count the number of workdays in between two dates, use the NETWORKDAYS function.
- NETWORKDAYS(start_date,end_date,holidays)
- Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.
Arguments:
Start_date is a date that represents the start date.
End_date is a date that represents the end date.
Holidays is an optional range of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contains the dates or an array constant of the serial numbers that represent the dates.
Example: Calculate elapsed business days
Cell A1 contains a start date, B1 an end date. "HolidaysList" is a named range with a list of federal holidays which do not count as business days (A6:A15 in the picture below). The formula to return the number of working days (excluding holidays and weekends) in between those dates is
=NETWORKDAYS(A1,B1,HolidaysList)-1
WORKDAY
WORKDAY is the complementary function to NETWORKDAYS. It takes a start date and number of work days, and returns the subsequent end date.
- WORKDAY(start_date,days,holidays)
- Returns a number that represents a date that is the indicated number of working days before or after a date (the starting date). Working days exclude weekends and any dates identified as holidays. If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.
Arguments:
Start_date is a date that represents the start date.
Days is the number of nonweekend and nonholiday days before or after start_date. A positive value for days yields a future date; a negative value yields a past date.
Holidays is an optional list of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contain the dates or an array constant of the serial numbers that represent the dates.
For an example of how to calculate future dates, see Calculate working days from start date. It's VBA-based, but almost directly translatable to a worksheet based formula.
Instead, I'll calculate a date in the past — I will calculate required "Ship On" dates. For example, you might ship packages and want to calculate when they need to be sent in order to arrive at a certain date. You know that most carriers won't deliver on federal holidays or weekends.
Cell A2 contains the turnaround time for a package. Cell B2 contains the date the package must arrive. "HolidaysList" is a named range with a list of federal holidays which do not count as business days (A5:A14 in the picture below). The date the package must be shipped can be calculated as follows:
=WORKDAY(B2, (A2-(A2+A2)),HolidaysList)-1
Note this formula returns a serial date, you must format the cell as a date in order to view it.
- Select cell C2 (the formula cell)
- Press Ctrl-1 or select 'Cells' from the 'Format' menu
- On the Number tab, select 'Date', choose how you want the date to display
- Click 'OK'
Bonus tip: Using the ground maps provided by UPS and Fedex, it is possible to construct WORKDAY formulas that will let you calculate this information on a per-address basis. Just create a table with a list of states and shipping turnaround times for each state, and a list of the destination addresses, then use INDEX/MATCH inside the WORKDAY formula to and refer to the destination state to lookup the number of days to advance the start date.
UPS Ground Map
FedEx Ground Map
INT
- INT(number)
- Rounds a number down to the nearest integer.
Arguments:
Number is the real number you want to round down to an integer.
MOD
- MOD(number,divisor)
- Returns the remainder after number is divided by divisor. The result has the same sign as divisor.
Arguments:
Number is the number for which you want to find the remainder.
Divisor is the number by which you want to divide number.
Example: Calculate elapsed hours between two dates
This formula will show the difference (in hours) between two cells with date/time values:
=IF(INT(B5)-INT(A5)<1,24*(MOD(B5,1)-MOD(A5,1)),((INT(B5)-INT(A5))*24)+24*(MOD(B5,1)-MOD(A5,1)))
Press Ctrl-1 to format the cell, on the Number tab choose "Custom," enter this format:
####.0# "hours"
The cell will show the number of hours passed between both dates/times, neatly formatted with a custom format displaying the word "hours" in the cell.
For example, suppose you have "1/12/2008 10:30 AM" in cell A5 and "1/13/08 11:00 AM" in cell B5 in the same worksheet (see below diagram). You can display the cells in any format you wish (i.e. General, Date, etc), as long as they contain real dates/times. The formula cell will display "24.5 hours".
The time value is in decimal format based on hours, so .5 hours = 30 minutes and .75 hours is 45 minutes.
To calculate the different in minutes, use this formula:
=IF(INT(B5)-INT(A5)<1,1440*(MOD(B5,1)-MOD(A5,1)),((INT(B5)-INT(A5))*1440)+1440*(MOD(B5,1)-MOD(A5,1)))
This formula works because there are 1,440 minutes in each day.
About JP
4 Comments:
I have a process which i need to measure elapsed time of. The steps are all listed and for each step the clerks are capturing the date and time of completion for the step (in separate cells). I want to have a formula that will automatically calculate the elapsed time between each step. Take note, the elapsed time should be based on an 8hour working day (7am to 4pm with an hour lunch in between). Also, i need to make provision for weekends to be excluded in the elapsed time.
Any ideas?
Have you checked out my UDF for calculating working hours?
http://www.jpsoftwaretech.com/blog/2009/03/time-difference-testing/
Need advice on best way to enter current date/time stamp into a cell when an adjacent cell is updated. For instance, Cell b2 contains a "item cost" and c2 contains date "3/1/2010". when I update my cost today (3/17/10) I want to update b2 manually and have c2 update automatically. However, likely I will not update b3, b4, b10, etc so no change to the date in the corresponding cells is desired.
You won't be able to do so without VBA. Something like this in the sheet module:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target.Address, Range("B2")) Is Nothing Then Range("C2").Value = Now End If End Sub