+ Reply to Thread
Results 1 to 6 of 6

How to Calculate time outside core hours regardless of Days

  1. #1
    Registered User
    Join Date
    09-05-2012
    Location
    Dubai
    MS-Off Ver
    Excel 2010
    Posts
    7

    How to Calculate time outside core hours regardless of Days

    Hi Everybody,

    I need help to calculate time outside contract hours. I have attached file with almost everything ready only i was not able to do is count of time outside contract hours. Out contract hours are from Morning 06:00AM to 20:00PM.
    for example in the attached file if you look at row 6 date 21st April 2012 time start is 04:45am & time finish is same day 0930:am so manually counting outside contract hours should be only 01:15 hours only but i am getting answer 22:45.

    Date Time Start Time Finish Duration hh:mm Outside Contract Hrs
    21-Apr-12 4:45 9:30 4:45:00 22:45

    Any help on the above is highly appreicated.

    Cheers,
    Mehul
    Attached Files Attached Files
    Last edited by mehulsagar; 09-09-2012 at 05:11 AM. Reason: Forgot to attach file

  2. #2
    Valued Forum Contributor
    Join Date
    04-03-2012
    Location
    East Sussex, UK
    MS-Off Ver
    Excel 2003:2010
    Posts
    893

    Re: How to Calculate time outside core hours regardless of Days

    Try pasting the below formula into cell E6 (as you have it set up as a table it will alter all relevant cells at the same time)

    =IF(B6<$B$1,$B$1-B6,0)+IF(C6>$B$2,C6-$B$2,0)

    Does that do what you need?
    If I've been of help, please hit the star

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

    Re: How to Calculate time outside core hours regardless of Days

    I don't think that works for all rows, Spencer, e.g. row 5 or row 8

    Try this formula

    =D5-(C5<B5)*(B$2-B$1)-MEDIAN(C5,B$1,B$2)+MEDIAN(B5,B$1,B$2)

    or the same thing in table notation

    =Table2[[#This Row],[Duration hh:mm]]-(Table2[[#This Row],[Time Finish]]<Table2[[#This Row],[Time Start]])*(B$2-B$1)-MEDIAN(Table2[[#This Row],[Time Finish]],B$1,B$2)+MEDIAN(Table2[[#This Row],[Time Start]],B$1,B$2)
    Audere est facere

  4. #4
    Registered User
    Join Date
    09-05-2012
    Location
    Dubai
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: How to Calculate time outside core hours regardless of Days

    Practice.xlsxAttachment 179331Hi spencer,

    Thanks for your quick response. Your formula worked almost except for one condition. Like in Row 14 & 24 if you see of the modified file


    Like Row 14 start time is 16:30 & finish time is 08:40 so total outside contract hrs it should show 10 hours from 20:00 to 0600 but it is showing 0

    Date Time Start Time Finish Duration hh:mm Outside Contract Hrs
    24-Apr-12 16:30 8:40 16:10:00 0:00
    29-Apr-12 5:00 4:00 23:00:00 1:00


    really appreciate your support for the above.

    Thanks & Cheers,
    Mehul
    Last edited by mehulsagar; 09-09-2012 at 06:21 AM. Reason: forgot to attach file

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

    Re: How to Calculate time outside core hours regardless of Days

    Try the formula I suggested below, I think that should work in all cases.....

  6. #6
    Registered User
    Join Date
    09-05-2012
    Location
    Dubai
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: How to Calculate time outside core hours regardless of Days

    Awesome Daddylonglegs really fomula worked well & correct.

    Thanks a Ton Longlegs & also thanks Spencer for your kind support & help.

    Just a request long legs if you can explain logic behind using median so i atleast know how it works & if i want i can use it for other purpose also.

    Thanks & Cheers,
    Mehul

+ 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