Likes Likes:  0
Page 1 of 6 123 ... LastLast
Results 1 to 10 of 59

Thread: Calculate only working hours between two dates excluding weekends

  1. #1
    New Member
    Join Date
    Oct 2009
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Calculate only working hours between two dates excluding weekends

    How should I calculate working hours between two dates? Say if start at 9/25/2009 7:26:13 PM and finish at 10/20/2009 9:46:13 AM, the function should return 245:20:00 because the working hours are from 8am to 11 PM (8 - 23), and there are weekends between the dates. Preferably the function should work like the NETWORKDAYS() function, but it should also include the time, not just the dates. And also how can I exclude holidays if there are any.

  2. #2
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    8,811
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculate only working hours between two dates excluding weekends

    If start date and time is in A1, start date and time in A2 . . .
    Code:
    =((NETWORKDAYS(A1,A2)-2)*15/24)+23/24-MOD(A1,1)+MOD(A2,1)-8/24
    returns 245:20:00 for your given dates/times.
    Format the cell containing the formula as [h]:mm:ss
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  3. #3
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    8,811
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculate only working hours between two dates excluding weekends

    By the way, my suggestion does not account for holidays. But the NETWORKDAYS function can be made to account for them.
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  4. #4
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    20,825
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculate only working hours between two dates excluding weekends

    You could simplify that a little Gerald, i.e.

    =(NETWORKDAYS(A1,A2)-1)*15/24+MOD(A2,1)-MOD(A1,1)

    Note: this only works assuming start and end time/dates will always be within working hours, if not you'll need a more complex formula....

  5. #5
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    8,811
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculate only working hours between two dates excluding weekends

    Quote Originally Posted by barry houdini View Post
    You could simplify that a little Gerald, i.e.

    =(NETWORKDAYS(A1,A2)-1)*15/24+MOD(A2,1)-MOD(A1,1)
    Yes you're right. I did think of that, but decided it would be better to show the formula in that longer format to help explain what it does.

    Note: this only works assuming start and end time/dates will always be within working hours, if not you'll need a more complex formula....
    LOL you're right
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  6. #6
    New Member
    Join Date
    Oct 2009
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculate only working hours between two dates excluding weekends

    Gerald,

    If the start time and end time are 8 Am and 6:30 PM, then wht changes should I make in the formula? Also, please let me know how I can exclude the holiday list too.

  7. #7
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    20,825
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculate only working hours between two dates excluding weekends

    Here's a generic formula

    =(NETWORKDAYS(A2,B2,holidays)-1)*(J$3-J$2)+MOD(B2,1)-MOD(A2,1)

    A2 = start time/date
    B2 = end time/date
    J2 = MF start time, e.g. 08:00
    J3 = MF end time e.g. 18:30
    holidays = named range containing holiday dates

  8. #8
    New Member
    Join Date
    Oct 2009
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculate only working hours between two dates excluding weekends

    Hey Barry does this formula exclude weekends too?

  9. #9
    MrExcel MVP
    Moderator
    barry houdini's Avatar
    Join Date
    Mar 2005
    Location
    England
    Posts
    20,825
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculate only working hours between two dates excluding weekends

    Yes, it will count hours between the two "timestamps" but including only the defined period on Mondays to Fridays, but excluding weekends and/or any holidays defined.

    As previously stated it's only guaranteed to give correct results if the start/end times/dates are within working hours.

    If the start or end could be at weekends or evenings, for instance, then you can still calculate the hours but you need a more complicated version of that formula.......

  10. #10
    New Member
    Join Date
    Nov 2009
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculate only working hours between two dates excluding weekends

    I am somehow not able to get it to work
    I think something wrong in the formating of my cells

    I am trying to use the formula given below
    =(NETWORKDAYS(H6,N6)-1)*($F$2-$E$2)+MOD(N6,1)-MOD(H6,1)
    here H6, N6 are in format of date time (like 3/5/2009 2:11:27 PM)
    and F2, E2 are in time format (9:00:00 AM and 6:00:00 PM)

    So when i calculate for start date
    3/2/09 7:29

    and end date
    3/31/09 18:17

    Its giving me wrong result as 7:29 ... (this result column i have formated as h:mm:ss )
    please help

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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