Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.
Forums » Excel Forums » Functions and Formulas » Working Hours

Working Hours

Previous | Next
Latest post Tue, Jun 17 2008 4:46 PM by Bob Phillips. 5 replies.

  • Tue, Jun 10 2008 6:56 AM

    • celianes
    • Top 75 Contributor
    • Joined on Tue, Jun 10 2008
    • Posts 4
    • Points 84

    Working Hours

    Reply |Contact

    Hi,

     

    I am trying to calculate the working hours between two dates, considering holidays

    Working hours start at 9 AM and end at 18 PM

    dATE 1:

    11/24/2006 15:40

    dATE 2:

    11/27/2006 8:56

    HOLIDAY

    11/25/2006

     MORE than happy to receive your point of view.

     

    Cheers,
    • Post Points: 21

  • Tue, Jun 10 2008 4:40 PM In reply to

    Re: Working Hours

    Reply |Contact |Mark as Answer

    =(NETWORKDAYS(date1+1,date2-1,holidays))*9
    +((WEEKDAY(date1,2)<6)*(NOT(ISNUMBER(MATCH(INT(date1),holidays,0))))*(MIN(MAX(0,--"18:00"-MOD(date1,1)),--"09:00"))*24)
    +((WEEKDAY(date2,2)<6)*(NOT(ISNUMBER(MATCH(INT(date2),holidays,0))))*(MIN(MAX(MOD(date2,1)-"09:00",0),--"09:00"))*24)

     

    Regards

    Bob

    • Post Points: 21

  • Thu, Jun 12 2008 2:45 AM In reply to

    • celianes
    • Top 75 Contributor
    • Joined on Tue, Jun 10 2008
    • Posts 4
    • Points 84

    Re: Working Hours

    Reply |Contact |Mark as Answer

     hi,

    many tks for your answer. i tested the formula and i got 80 hours, using the cell custom format:hh:mm:ss. I calculated manually the difference and it gave less than that.

    any other thoughts on how i can calculate this working hours?

    Cheers,
    • Post Points: 21

  • Thu, Jun 12 2008 3:18 AM In reply to

    Re: Working Hours

    Reply |Contact |Mark as Answer

    You would need to tell me what inputs you had, what you got and what you expected. I tested it with yyour previous example and I got 29.3333 which is what I expected.

    Regards

    Bob

    • Post Points: 21

  • Tue, Jun 17 2008 10:16 AM In reply to

    • celianes
    • Top 75 Contributor
    • Joined on Tue, Jun 10 2008
    • Posts 4
    • Points 84

    Re: Working Hours

    Reply |Contact |Mark as Answer

    Hello,

    i calculated manually the result and i have got 20 hours

    start hours 9:00 AM
    end hours 18:00:00 PM
    dATE 1: 11/24/2006 15:40 PM
    dATE 2: 11/27/2006 20:56
    HOLIDAY 11/25/2006

    The correct answer should be 20 hours ( 2 working hours -26 and 27 of Nov 2006 and the 2:40 hours form the 24

    maybe if we would use the excel and share it i could better understand how you obtained your values?

    Please help out!

    • Post Points: 21

  • Tue, Jun 17 2008 4:46 PM In reply to

    Re: Working Hours

    Reply |Contact |Mark as Answer

    I make that 2:20 on 24th, 9 on 26th, 9 on 27th, giving 20.3333 which is what the formula gives.

    Regards

    Bob

    • Post Points: 5
Page 1 of 1 (6 items) | RSS
Copyright Excel User Group and the relevant contributors, 2008. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.