+ Reply to Thread
Results 1 to 10 of 10

Formula to calculate working hours between two dates not working correctly?

  1. #1
    Registered User
    Join Date
    03-07-2013
    Location
    england
    MS-Off Ver
    Excel 2010
    Posts
    19

    Formula to calculate working hours between two dates not working correctly?

    Hi Everyone,

    I'm new to this forum and need some help with a formula in my worksheet.

    Basically this is to calculate the total working hours between two dates. In many occasions this formula works, however there is a certain instance when it doesn't.

    The formula is:

    =(NETWORKDAYS([@[Date/Time Received]],[@[Date/Time Resolved]])-1)*($U$2-$T$2)+MOD([@[Date/Time Resolved]],1)-MOD([@[Date/Time Received]],1)

    Like I say, 99/100 times this works but it appears that when there is only one day separating the two dates, and the time on the first date is later to that on the second date then it returns a value #########.
    So for example 06/03/13 12:00 - 07/03/13 12:00 will correctly display 24 hours 1 minute. But if it were to be 06/03/13 12:00 - 07/03/13 11:59 then it wouldn't.

    I was just wondering if anybody could assist me in finding a solution for this as it is essentially that I keep a log of activity.

    P.S. If you are wondering what is at U2 and T2, they are the opening and closing times.

    Thanks a lot.

  2. #2
    Registered User
    Join Date
    03-07-2013
    Location
    england
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Formula to calculate working hours between two dates not working correctly?

    Update.

    A more accurate description of my problem is that the formula will only fail if the Date/Time received time falls outside of the closing time.
    This works perfectly if there is a day that separates it, but if I receive it after the closing time and resolve it the next day inside the opening times it will not work!

    Please help!

    Thanks

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    mysore
    MS-Off Ver
    Excel 2007
    Posts
    4,917

    Re: Formula to calculate working hours between two dates not working correctly?

    Pl attach a dummy file.

  4. #4
    Registered User
    Join Date
    03-07-2013
    Location
    england
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Formula to calculate working hours between two dates not working correctly?

    As you can see, very dodgy. Works sometimes and others is doesn't.
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    07-20-2011
    Location
    mysore
    MS-Off Ver
    Excel 2007
    Posts
    4,917

    Re: Formula to calculate working hours between two dates not working correctly?

    Pl see the attached file with formula in "d" column
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    03-07-2013
    Location
    england
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Formula to calculate working hours between two dates not working correctly?

    Thanks for helping!

    Unfortunately this formula isn't bringing back the value that I wanted. I want to calculate the total work hours/minutes that go in to completing a job. For example in the first row, the value should be 7:04. Because, although we received the issue at 22:45 the previous night we only began working on it at 8:30. Unfortunately it seems to be when we log an issue outside of work hours and then complete it the following day during work hours.

    I want to know if there is any way around this.

    Thanks again for the effort, but is there anything else I could do to calculate this?

  7. #7
    Forum Expert
    Join Date
    07-20-2011
    Location
    mysore
    MS-Off Ver
    Excel 2007
    Posts
    4,917

    Re: Formula to calculate working hours between two dates not working correctly?

    I have changed formula in D column .Pl see
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    02-05-2013
    Location
    Mumbai
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Formula to calculate working hours between two dates not working correctly?

    please see its helpful or not
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    03-07-2013
    Location
    england
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Formula to calculate working hours between two dates not working correctly?

    Quote Originally Posted by kvsrinivasamurthy View Post
    I have changed formula in D column .Pl see
    Fantastic, great job.

    Thanks so much!

  10. #10
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Formula to calculate working hours between two dates not working correctly?

    If U2 is the opening time and T2 the closing time then you have those two the wrong way round in your formula (and E2 and F2 on the attachment). That won't be a problem when both dates are the same but over longer periods you will get incorrect results, so formula in attachment should be as follows:

    =(NETWORKDAYS(Table1[[#This Row],[Date/Time Received]],Table1[[#This Row],[Date/Time Resolved]])-1)*($F$2-$E$2)+MOD(Table1[[#This Row],[Date/Time Resolved]],1)-MOD(Table1[[#This Row],[Date/Time Received]],1)

    That will count all the hours between 08:30 and 17:00 on your attachment........but as you say it doesn't work if the dates/times are outside work hours. Assuming that only the received time can be outside work hours you can change to this version to calculate correctly in all cases:

    =(NETWORKDAYS(Table1[[#This Row],[Date/Time Received]],Table1[[#This Row],[Date/Time Resolved]])-1)*($F$2-$E$2)+MOD(Table1[[#This Row],[Date/Time Resolved]],1)-MEDIAN(MOD(Table1[[#This Row],[Date/Time Received]],1)*NETWORKDAYS(Table1[[#This Row],[Date/Time Received]],Table1[[#This Row],[Date/Time Received]]),E$2,F$2)
    Audere est facere

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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