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 Thu, Sep 4 2008 1:24 PM by Bob Phillips. 9 replies.

  • Tue, Jun 10 2008 6:56 AM

    • celianes
    • Top 50 Contributor
    • Joined on Tue, Jun 10 2008
    • Posts 6
    • Points 126

    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 50 Contributor
    • Joined on Tue, Jun 10 2008
    • Posts 6
    • Points 126

    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 50 Contributor
    • Joined on Tue, Jun 10 2008
    • Posts 6
    • Points 126

    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: 21

  • Thu, Sep 4 2008 4:19 AM In reply to

    • celianes
    • Top 50 Contributor
    • Joined on Tue, Jun 10 2008
    • Posts 6
    • Points 126

    Re: Working Hours

    Reply |Contact |Mark as Answer

    Hello friend,

    I am really trying to understand how can i make your formula work for the data below, same context as earlier

    Date 1

    11/27/2006 10:50 AM
     

    Date 2

    11/27/2006 10:57 AM

     Holiday

    0

    Can you send me your formula in excel please, i get different numbers.

    If you have a phone line where i can call you, more than happy to do it.
    • Post Points: 21

  • Thu, Sep 4 2008 8:35 AM In reply to

    Re: Working Hours

    Reply |Contact |Mark as Answer

    I have just put 24/11/2008  15:40:00 in A1, 27/11/2008  20:56:00 in A2, and 25/11/2008 in the first cell of the holidays range and then the formula

     

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

    returns 20.3333, which is correct.

    I can't see what else there is to say.

    You wouldn't want to call me, I am in the UK.

    Regards

    Bob

    • Post Points: 21

  • Thu, Sep 4 2008 8:50 AM In reply to

    • celianes
    • Top 50 Contributor
    • Joined on Tue, Jun 10 2008
    • Posts 6
    • Points 126

    Re: Working Hours

    Reply |Contact |Mark as Not Answer

    Hi,

     

    Happy to call you and discuss:

    you can send me the phone number on my email; lucy2you76@yahoo.com
    • Post Points: 21

  • Thu, Sep 4 2008 1:24 PM In reply to

    Re: Working Hours

    Reply |Contact |Mark as Answer

    I've emailed you an example workbook.

    Regards

    Bob

    • Post Points: 5
Page 1 of 1 (10 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.