1. Forum
  2. Usenet
  3. MSOFT.PUB.EXCEL.WORKSHEET

  • WORK HOURS DIFFERENCE BETWEEN TWO DATES

    From dme01@khatuwala.net@21:1/5 to benwi...@googlemail.com on Wed Mar 18 01:16:26 2020
    On Thursday, April 9, 2015 at 1:35:31 PM UTC+5:30, benwi...@googlemail.com wrote:
    " If these dates were public holidays then the real start/end date is a day later/earlier and in any case I'm struggling to work it out otherwise".

    Mike's formula is great, but having issue when start date is a public holiday, does anyone know how to resolve this?

    Thank you.

    =(NETWORKDAYS.INTL(B5,C5,11)-2)*(B2-B1)+(B2-MOD(B5,1))+(MOD(C5,1)-B1)
    use this formula to clear your query if you need to add office holidays then add Holiday list in networkdays formula

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Sarah Saleh@21:1/5 to Mike H on Wed Aug 31 02:35:04 2022
    On Wednesday, January 16, 2008 at 2:44:01 PM UTC+3, Mike H wrote:
    Hi,
    Try this =(NETWORKDAYS(A1,A2)-1)*(B2-B1)+IF(NETWORKDAYS(A2,A2),MEDIAN(MOD(A2,1),B2,B1),B2)-MEDIAN(NETWORKDAYS(A1,A1)*MOD(A1,1),B2,B1)
    Where:-
    A1= Earlier date/time
    A2= Later date/time
    B1 = 08:00
    B2 = 17:00
    Mike
    "CHRISTI" wrote:
    I need to calculate the total WORK-hours (08:00-17:00) between two date/time-stamps;
    -excluding WEEKENDS
    -excluding PUBLIC HOLIDAYS

    eg#1.: A1=START DATE/TIME & A2=END DATE/TIME & A3=RESULT ([hh]:mm)
    A1 11-01-2008 09:00:00
    A2 11-01-2008 11:00:00
    A3 02:00

    eg#2.: A1=START DATE/TIME & A2=END DATE/TIME & A3=RESULT ([hh]:mm)
    A1 11-01-2008 09:00:00
    A2 14-01-2008 11:00:00
    A3 11:00

    eg#3.: A1=START DATE/TIME & A2=END DATE/TIME & A3=RESULT ([hh]:mm)
    A1 14-01-2008 09:00:00
    A2 16-01-2008 11:00:00
    A3 20:00

    Hey there,

    It seems that this method is not working anymore, do you have another solution?

    Thanks,

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)