Excel Date and TIme Calculations

Topic

New Reply

While researching for a way to calculate costs for individual projects that can last up to months, I was looking through the function options in Excel and came across the “Networkdays.INTL” function thinking that this may be one of the functions I will need to dissect the time period. However, while testing the function out in order to get familiar with it, I am getting results that do not seem to make sense. The description for this function in Excel Help explains “Returns the number of whole workdays between two dates using parameters to indicate which and how many days are weekend days. Weekend days and any days that are specified as holidays are not considered as workdays” but when I enter a start date and an identical end date the result is “1” rather than “0” and when I enter a start date that is one day earlier than the end date the result is “2” rather than “1”. (Please see cell B8 in the attachment). I am hoping you can help me with this function to determine what I am doing wrong to get these results.
I am also looking for suggestions (or even answers) of ways to proceed in order to accomplish this task. The catch though is that the costs for these projects fluctuate through the course of a week, hours of the day, and also each month. For example a project lasting 1 week can have three different costs depending upon the time of the day and/or the day of the week (5X16 Day shift hours, 7X8 night shift hours, and 2X16 weekend hours other than night shift.
Ultimately the prices (B4:B6) would be pulled from a table on the sheet named “Prices” depending upon the dates entered in cells B2 and C2 of the worksheet named “Test”. The key to differentiating this will be in how far each time period can be dissected to reflect the four variables (three in cells A4:A6 along with a project continuing across multiple months)
This task looks rather involved to me given that I am sort of a weekend warrior when it comes to determining a way to proceed with this. I know I have asked questions in the past regarding methods in Excel and I have been pleasantly surprised on how short the formulas really needed to be once I get a solution from you. In fact the formulas in cells D2 and E2 are based upon previous help from you. Any help you can supply in this regard would be greatly appreciated.

Reply | Quote

Replies

Just subtract 1 from the number.
=NETWORKDAYS.INTL(B2,C2)-1

The calculation ignores the time. Think of it as the StartDate begins right after Midnight so is the start of the day and the EndDate is right before midnight of that day (End of the day), so with the same start and end date one entire day has been completed. The calc is akin to EndDate-StartDate+1 – NumHolidaysBetween – NumWeekendDaysBetween.

You don’t seem to want the “+1”, so subtract it.

Steve

Reply | Quote

Thank you Steve for the quick reply and the help.

Reply | Quote

Is there a way to itemize the months for a given period? For example, is there a way to calculate how many days of March, of April, and of May there are in the period of 03/08/14 to 05/25/14?

Reply | Quote

A formula like this will work:

Code:

=DATE(2014,MonthNum+1,0)-DATE(2014,MonthNum,0)-(MAX(StartDate-DATE(2014,MonthNum,1),0))-(MAX(DATE(2014,MonthNum+1,0)-EndDate,0))

Replace the term “MonthNum” with the month of interest (3,4,5, etc) and StartDate with the start date 3/8/14 and enddate with the end date…
The values (including the years) could all be stored in cells for easy reference.

Steve

Reply | Quote

Thank you Steve

Reply | Quote