Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Calculating working hours between two dates

This is a discussion on Calculating working hours between two dates within the Excel Questions forums, part of the Question Forums category; How should I calculate working hours between two dates? Say if start at 26 july at 15:00 and finish at ...

  1. #1
    New Member
    Join Date
    Jul 2002
    Posts
    15

    Default

    How should I calculate working hours between two dates? Say if start at 26 july at 15:00 and finish at 29 july at 10:00, the function should return 4 hours because the working hours are from 8am to 5pm (8 - 17), and there is a weekend 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, if have to add say 8 hours to a date, how should I calculate the result? Also this function should aware of the working hours and holidays, so it should ignore those times.

    regards,
    Petteri Toukoniitty

  2. #2
    IML
    IML is offline
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,744

    Default

    You could try
    =IF(NETWORKDAYS(A1,B1)=1,(MIN(Z1,MOD(B1,1))-MAX(Y1,MOD(A1,1))),(NETWORKDAYS(A1,B1)-2)*(600/1440)+(Z1-MAX(MOD($A$1,1),Y1))+MIN(MOD($B$1,1),Z1)-Y1)

    where
    a1 is start time
    b1 is stop time
    y1 is start of day (8:00)
    z1 is end of day (17:00)

    You can certainly change y1 and z1 to defined names if you wish. I beleive this formula should work in all cases, except if the start time falls on a weekend. Please let me know if you find some instances it doesn't work and we can tweak it.

  3. #3
    New Member
    Join Date
    Jul 2002
    Posts
    15

    Default

    This works fine, thank you! I'm just wondering, how to add hours to some date. So if some program starts at 2002-07-26 9:00 and lasts for ten hours, how do I calculate is so that it excludes the non-workign hours and holidays? So in this case the answer would be 2002-07-29 10:00, because the working hours are 8 - 17.

    Petteri Toukoniitty

    On 2002-07-25 07:27, IML wrote:
    You could try
    =IF(NETWORKDAYS(A1,B1)=1,(MIN(Z1,MOD(B1,1))-MAX(Y1,MOD(A1,1))),(NETWORKDAYS(A1,B1)-2)*(600/1440)+(Z1-MAX(MOD($A$1,1),Y1))+MIN(MOD($B$1,1),Z1)-Y1)

    where
    a1 is start time
    b1 is stop time
    y1 is start of day (8:00)
    z1 is end of day (17:00)

    You can certainly change y1 and z1 to defined names if you wish. I beleive this formula should work in all cases, except if the start time falls on a weekend. Please let me know if you find some instances it doesn't work and we can tweak it.

  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582

    Default

    Hi Bubuto

    Read this page, and the MS links at the bottom. After that you will be a full bottle on Dates and Times in Excel.

    http://www.ozgrid.com/Excel/ExcelDateandTimes.htm

  5. #5
    IML
    IML is offline
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,744

    Default

    here is my attempt, not fully tested. This assumes time date in A1, hours likst in B1 (ie enter 10 for ten hours, 10.5 four 10:30, etc). Open time is still Y1 and close time Z1.

    =IF(MAX(0,B1/24-MIN($Z$1-$Y$1,MAX($Z$1-MOD(A1,1),0))),WORKDAY(A1,INT(MAX(0,B1/24-MIN($Z$1-$Y$1,MAX($Z$1-MOD(A1,1),0)))/($Z$1-$Y$1))+1)+$Y$1+MOD(MAX(0,B1/24-MIN($Z$1-$Y$1,MAX($Z$1-MOD(A1,1),0)))/($Z$1-$Y$1),1)*($Z$1-$Y$1),A1+(B1/24))

  6. #6
    Board Regular megnin's Avatar
    Join Date
    Feb 2002
    Location
    Fort Lauderdale
    Posts
    340

    Default

    IML,

    =IF(NETWORKDAYS(A1,B1)=1,(MIN(Z1,MOD(B1,1))-MAX(Y1,MOD(A1,1))),(NETWORKDAYS(A1,B1)-2)*(600/1440)+(Z1-MAX(MOD($A$1,1),Y1))+MIN(MOD($B$1,1),Z1)-Y1)

    Your formula works great for full hours. How would I modify it to show minutes as well?

    Thank you!

  7. #7
    Board Regular megnin's Avatar
    Join Date
    Feb 2002
    Location
    Fort Lauderdale
    Posts
    340

    Default

    IML,

    Disregard my last, I don't know why some of my times were showing up as whole hours instead of with minutes, unless it's related to my current problem...

    If the start time is beyond working hours (Y1, Z1) then I get ############. A negative date?

    Do you know how I could allow for start and ending times outside the working hours?

    Thanks.

  8. #8
    Board Regular megnin's Avatar
    Join Date
    Feb 2002
    Location
    Fort Lauderdale
    Posts
    340

    Default Re: Calculating working hours between two dates

    Anyone,

    I've been using this formula for quite a while now and it has worked fine.
    Code:
    =IF(((J2-A2)<0),"OPEN",MIN(MOD(J2,1),$Z$1)-MAX(MOD(A2,1),$Y$1)+(SUM(IF(WEEKDAY(INT(A2)-1+ROW(INDIRECT("1:"&(INT(J2)-INT(A2))+1)))=TRANSPOSE($Y$3:$Y$7),1,0))-1)*($Z$1-$Y$1))
    Now they want to INCLUDE weekends. No other change, just count weekends. Same working hours, etc.

    How could I change the formula to count weeks as well. All 7 days.

    Thanks!!

    I tried to figure out what I needed to delete to make it count all 7 days, but it just made my head hurt.
    David

  9. #9
    Board Regular megnin's Avatar
    Join Date
    Feb 2002
    Location
    Fort Lauderdale
    Posts
    340

    Default Re: Calculating working hours between two dates

    Hi,

    StartTime= A2
    EndTime= J2
    StartWorking Hours= $Y$1
    EndWorking Hours= $Z$1
    Y2 = "1" (Sunday)
    Y3 = "2" (Monday)
    Y4 = "3"
    Y5 = "4"
    Y6 = "5"
    Y7 = "6"
    Y8 = "7" (Saturday)

    How do you calculate working hours (and minutes) between start time and end time. The times may span two or more days.

    I've been using this formula for quite a while now and it has worked fine, but excluded weekends.
    Code:
    =IF(((J2-A2)<0),"OPEN",MIN(MOD(J2,1),$Z$1)-MAX(MOD(A2,1),$Y$1)+(SUM(IF(WEEKDAY(INT(A2)-1+ROW(INDIRECT("1:"&(INT(J2)-INT(A2))+1)))=TRANSPOSE($Y$3:$Y$7),1,0))-1)*($Z$1-$Y$1))
    Now they want to INCLUDE weekends. No other change, just count all 7 days instead of just mon-fri.

    Thanks!!

  10. #10
    New Member
    Join Date
    Nov 2009
    Posts
    2

    Default Re: Calculating working hours between two dates

    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

Page 1 of 3 123 LastLast

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
  •  


DMCA.com