Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Dec 2005
    Posts
    37
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Excel Date and TIme Calculations

    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.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  3. The Following User Says Thank You to sdckapr For This Useful Post:

    workingonit (2014-05-18)

  4. #3
    Lounger
    Join Date
    Dec 2005
    Posts
    37
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Thank you Steve for the quick reply and the help.

  5. #4
    Lounger
    Join Date
    Dec 2005
    Posts
    37
    Thanks
    7
    Thanked 0 Times in 0 Posts
    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?

  6. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  7. #6
    Lounger
    Join Date
    Dec 2005
    Posts
    37
    Thanks
    7
    Thanked 0 Times in 0 Posts
    Thank you Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •