+ Reply to Thread
Results 1 to 9 of 9

Caculating the Time difference from Start & end date/time excludin weekends & non ofce hrs

  1. #1
    Registered User
    Join Date
    07-01-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2003
    Posts
    5

    Caculating the Time difference from Start & end date/time excludin weekends & non ofce hrs

    Im using an Excel 2003 & i need to calculate the net process or respond time excluding
    1. weekends &
    2. non working hours

    My excel sheets contains:-
    A2:- Start date
    B2:- Start time
    C2:- Stop date
    D2:- Stop time
    E2:- needs to show the difference of the time in minutes
    (If required, the start date & time can be combined to one cell)

    Working hours will be:-
    Monday to Friday:- 06:30 to 22:00 hrs (IST)
    Saturday 09:30 to 13:00 hrs (IST)

    Weekends with be:-
    Saturdays (After 13:00 hrs) + Sundays (Whole day) + Monday 06:30 hrs

    Plse help me out whether there is any possibilities to set up such formula in excel 2003.
    This will reduce lots of work from my end

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,864

    Re: Caculating the Time difference from Start & end date/time excludin weekends & non ofce

    Lookup NETWORKDAYS in Help.
    Ben Van Johnson

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

    Re: Caculating the Time difference from Start & end date/time excludin weekends & non ofce

    Assuming that your start/stop times/dates will always be within working hours then try this formula in E2

    =(SUMPRODUCT(INT((WEEKDAY(A2-{2,3,4,5,6,7})+C2-A2)/7),{15.5,15.5,15.5,15.5,15.5,3.5})+IF(WEEKDAY(A2)=7,9.5,6.5)-IF(WEEKDAY(C2)=7,13,22))/24+D2-B2

    format E2 as [h]:mm to see a result like 6:30.......or to display that as minutes like 390 custom format E2 as [m]
    Audere est facere

  4. #4
    Registered User
    Join Date
    07-01-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Caculating the Time difference from Start & end date/time excludin weekends & non ofce

    Dear Legs,

    The below formula works. However, just wanna inform you that my start date & time cannot be only in working days & hours. It will be outside the working hours or even in weekend.

    So, can you plse advise the formula for the same too.

  5. #5
    Registered User
    Join Date
    07-01-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Caculating the Time difference from Start & end date/time excludin weekends & non ofce

    Can anybody assist on this formula

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

    Re: Caculating the Time difference from Start & end date/time excludin weekends & non ofce

    This version should accommodate any start/end times/dates

    =(SUMPRODUCT(INT((WEEKDAY(A2-{2,3,4,5,6,7})+C2-A2)/7),{15.5,15.5,15.5,15.5,15.5,3.5})-IF(WEEKDAY(A2)=1,0,IF(WEEKDAY(A2)=7,MEDIAN(B2*24-9.5,0,3.5),MEDIAN(B2*24-6.5,0,15.5)))-IF(WEEKDAY(C2)=1,0,IF(WEEKDAY(C2)=7,MEDIAN(13-D2*24,0,3.5),MEDIAN(22-D2*24,0,15.5))))/24

  7. #7
    Registered User
    Join Date
    07-01-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Caculating the Time difference from Start & end date/time excludin weekends & non ofce

    The above formula works. However, there are still some condition wherein the time still shows incorrect format.

    Start date Start time Stop date Stop time Difference Comments
    05-07-2012 15:46 06-07-2012 10:46 10:30 Working fine
    06-07-2012 23:00 07-07-2012 10:30 1:00 Working fine
    07-07-2012 14:00 09-07-2012 07:30 1:00 Weekends timing works fine
    04-07-2012 18:00 06-07-2012 10:00 23:00 Two difference time calculation working fine.
    04-07-2012 18:00 06-07-2012 16:00 5:00 Not showing the calculation properly
    time calculation should be 4hrs(04-07-2012)+15:30 hrs(05-07-2012)+9:30 hrs(06-07-2012)= 29 hrs
    However, it only shows 5 hrs, excluding the 24 hrs (which is one day)

    I tried to format the cell using [h] to show it in hours, but still the result remains the same.
    But when i tried to convert it in the minutes, (E2*1440), the output is perfect

    Can you plse let me know how can i format the cell in the Difference (e2) to show the time as 29 hrs i/o 5 hrs for the 5th set-up highlighted in Blue

  8. #8
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Caculating the Time difference from Start & end date/time excludin weekends & non ofce

    You get better help if you post an Excel-example of your workbook, without confidential information.

    Make sure the workbook demonstrates your desired results if possible, or just highlight the cells you're trying to fix.

    Use BEFORE/AFTER sheets if that helps make it clearer.

    Why don't you splt the input for each day?
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

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

    Re: Caculating the Time difference from Start & end date/time excludin weekends & non ofce

    To correctly show hours over 24 then you need to custom format as [h]:mm - it looks like you have just h:mm

+ 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