+ Reply to Thread
Results 1 to 4 of 4

Calculating working hours between two dates and business hours

  1. #1
    Forum Contributor
    Join Date
    03-03-2004
    Location
    mumbai
    MS-Off Ver
    2003,2007
    Posts
    151

    Calculating working hours between two dates and business hours

    Hi,
    I have gone through the forum rule and posting this thread again as the answer provided in the other thread is not giving me the desired result.
    I have two date and business hour is from evening 5 PM till Morning 5:30 AM. I have used the below formula and it is giving me 11:30 hours whereas the hour should be 0 as these 2 dates fall on a Sunday.
    Start date is 5/31/2015 9:10:00 AM and End date is 5/31/2015 11:54:00 AM.

    =(NETWORKDAYS(G13,D13)-1)*($O$1-$N$1)+IF(NETWORKDAYS(D13,D13),MEDIAN(MOD(D13,1),$O$1,$N$1),$O$1)-MEDIAN(NETWORKDAYS(G13,G13)*MOD(G13,1),$O$1,$N$1)

    The second result provided is 0 hours again which is wrong as the start date is 5/29/2015 9:20:00 PM and end date is 5/29/2015 10:10:00 PM.
    thank you

  2. #2
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: Calculating working hours between two dates and business hours

    I guess nothing wrong with the formula, maybe just custom formatting as [h]:mm

  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Calculating working hours between two dates and business hours

    The formula you are using isn't designed to work with a working hours period that passes midnight (like 5 PM to 5:30 AM). Try using this formula which will cater for those periods

    =(NETWORKDAYS(G13-$N$1,D13-$N$1)-1)*MOD($O$1-$N$1,1)+IF(NETWORKDAYS(D13-$N$1,D13-$N$1),MIN(MOD(D13-$N$1,1),MOD($O$1-$N$1,1)),MOD($O$1-$N$1,1))-MIN(NETWORKDAYS(G13-$N$1,G13-$N$1)*MOD(G13-$N$1,1),MOD($O$1-$N$1,1))
    Audere est facere

  4. #4
    Forum Contributor
    Join Date
    03-03-2004
    Location
    mumbai
    MS-Off Ver
    2003,2007
    Posts
    151

    Re: Calculating working hours between two dates and business hours

    Thanks daddy, your formula is working fine.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Calculating working hours betwen two dates
    By dpathak in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-12-2013, 12:44 PM
  2. Calculating working hours between two dates/times!
    By JDGreen17 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-22-2013, 07:46 AM
  3. Replies: 6
    Last Post: 01-06-2013, 07:13 AM
  4. Replies: 1
    Last Post: 11-29-2011, 12:19 AM
  5. Calculating Business Hours Between 2 Dates
    By tanya216 in forum Excel General
    Replies: 3
    Last Post: 04-11-2006, 10:30 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1