You already knew that Excel can do straight date math, but it can also take into account holidays to find the number of working days between two dates. Tech blogger Chandoo points out the =networkdays(start date, end date, predefined list of holidays) formula, which turns out the number of days minus holidays (and vacation) between the start and end date—quite useful for work project spreadsheets. Chandoo offers several other “hey I didn’t know that” Excel tricks too, like filtering unique items from a list, sorting data from left to right, freezing panes, and coloring your worksheet tabs.
Omega
January 1, 2009 at 10:35 AM
The number of working days between two dates can be calculated using a custom function in Excel. See here: http://www.excelexchange.com/WorkingDays.html
Report PermalinkDarren Eves
April 15, 2009 at 6:05 PM
you can also emulate networking days with a single cell array formula (note holidays are stored in a column, and you enter it with CTRL-SHIFT-ENTER)
=(EndDate-StartDate+1)-SUMPRODUCT(–(MOD(ROW(INDIRECT(StartDate&”:”& EndDate)),7)1)*–(-1+StartDate+ROW(INDIRECT(“1:” & ( EndDate-StartDate+1)))=TRANSPOSE(Holidays)))
Report Permalink