Calculate turn around time excluding Sundays and public holidays

{ 206 Comments }

Assume a two column database showing starting date/time and ending data/time (Data/time stamp appear in a single cell).  Given a list of public holidays in a year and starting and ending work times, one may want to know the turn around time excluding Sundays and public holidays.

You may refer to my solution in this workbook.

Leave a Comment Cancel reply

Your email address will not be published.

*

  • Hi… Incase if I have to exclude saturdays also, whether I’ve to apply other weekday formula set??? Can you give a formula for that…

    • Hi,

      If you wish to exclude Saturday, Sundays and public holidays, then try this

      1. Ensure that the public holidays in range B2:B9 are non Saturday, non Sunday public holidays
      2. In cell I2, the formula will become

      =IF(OR(WEEKDAY(INT(A2),2)>=6,COUNTIF(‘Public Holidays & work hours’!$B$2:$B$9,INT(Data!A2))=1),0,IF(‘Public Holidays & work hours’!B$14-MOD(Data!A2,1)=6,COUNTIF(‘Public Holidays & work hours’!$B$2:$B$9,INT(Data!B2))=1,INT(B2)=INT(A2)),0,MAX(MIN(MOD(B2,1),’Public Holidays & work hours’!$B$14),’Public Holidays & work hours’!$B$13)-‘Public Holidays & work hours’!B$13)

      4. In cell K2, the formula will remain the same
      5. In cell L2, the formula will become

      =IF(INT(B2)-INT(A2)=6)))*(‘Public Holidays & work hours’!B$14-‘Public Holidays & work hours’!B$13)

      6. In cell M2, the formula will remain the same.

      Hope this helps.

      • Hi,

        I’m trying to collate this formula in D2 cell but it displays error. Can you please give formula for D2 too.

        • Hi,

          In cell D2, try this formula

          =IF(OR(WEEKDAY(INT(A2),2)>=6,COUNTIF(‘Public Holidays & work hours’!$B$2:$B$9,INT(Data!A2))=1),0,IF(‘Public Holidays & work hours’!B$14-MOD(Data!A2,1)<0,0,MIN('Public Holidays & work hours'!B$14-MAX(MOD(Data!A2,1),'Public Holidays & work hours'!$B$13),B2-MAX(A2,INT(A2)+'Public Holidays & work hours'!$B$13))))+IF(OR(WEEKDAY(INT(B2),2)>=6,COUNTIF(‘Public Holidays & work hours’!$B$2:$B$9,INT(Data!B2))=1,INT(B2)=INT(A2)),0,MAX(MIN(MOD(B2,1),’Public Holidays & work hours’!$B$14),’Public Holidays & work hours’!$B$13)-‘Public Holidays & work hours’!B$13)+IF(INT(B2)-INT(A2)<=1,0,(INT(B2)-INT(A2)-1)*('Public Holidays & work hours'!B$14-'Public Holidays & work hours'!B$13))-(IF(INT(B2)-INT(A2)<=1,0,SUMPRODUCT(1*(WEEKDAY(ROW(INDIRECT(INT(A2)+1&":"&INT(B2)-1)),2)>=6)))*(‘Public Holidays & work hours’!B$14-‘Public Holidays & work hours’!B$13))-(IF(INT(B2)-INT(A2)<=1,0,SUMPRODUCT(1*(ISNUMBER(MATCH(ROW(INDIRECT(INT(A2)+1&":"&INT(B2)-1)),('Public Holidays & work hours'!$B$2:$B$9),0))))*('Public Holidays & work hours'!B$14-'Public Holidays & work hours'!B$13))) Hope this helps.

          • The following formula is not excluding holidays

            =IF(OR(WEEKDAY(INT(A2),2)>=6,COUNTIF(‘Public Holidays & work hours’!$B$2:$B$9,INT(Data!A2))=1),0,IF(‘Public Holidays & work hours’!B$14-MOD(Data!A2,1)=6,COUNTIF(‘Public Holidays & work hours’!$B$2:$B$9,INT(Data!B2))=1,INT(B2)=INT(A2)),0,MAX(MIN(MOD(B2,1),’Public Holidays & work hours’!$B$14),’Public Holidays & work hours’!$B$13)-’Public Holidays & work hours’!B$13)+IF(INT(B2)-INT(A2)< =1,0,(INT(B2)-INT(A2)-1)*('Public Holidays & work hours'!B$14-'Public Holidays & work hours'!B$13))-(IF(INT(B2)-INT(A2)=6)))*(‘Public Holidays & work hours’!B$14-’Public Holidays & work hours’!B$13))-(IF(INT(B2)-INT(A2)<=1,0,SUMPRODUCT(1*(ISNUMBER(MATCH(ROW(INDIRECT(INT(A2)+1&”:”&INT(B2)-1)),(‘Public Holidays & work hours’!$B$2:$B$9),0))))*(‘Public Holidays & work hours’!B$14-’Public Holidays & work hours’!B$13)))

          • Hi,

            I am not sure of what changes you have made to my formula but it is differently different from the one I have shown in cell D2 of the file attached in the original post. One small difference which I can observe is the bracketing in the formula.

            In cell D2 of the file in my original post, the value in cell D2 is 00:35:00 for a start time of 01/04/2012 09:12 and End time of 02/04/2012 08:35. Since 01/04/2012 was a Sunday, the hours on Sunday have been excluded.

            I suggest you use my formula in the attached file.

          • This formula seems to work perfectly for what I’m doing (calculating Time to Restore for support groups/trouble tickets), but I am getting a couple odd results. On rows were the ticket is opened and closed before business hours, i get a negative result. Ex: start at 8/11/14 03:00 and end at 8/11/14 05:21 gives an answer of -0.11 hours (or -2:38). My expected result would be “0:00” I would really appreciate help on this as I’m so lost on these deep formulas.

          • I’m sorry to ask for help again, but I was using the formula without the helper columns (I can adjust on my end if necessary to use them) but also need to remove the saturday hours? Is there a simple adjustment to the “sunday hours” column I can do to have it include both sat and sun? I REALLY appreciate the help here btw, it amazes me that you can come up with these formulas!

      • Hi Ashish,

        Please help me with the formula which calculates the difference between two dates & Time which also excludes the weekend (Saturday & Sunday) and list of holidays from 2nd tab. Our working hours are morning 8:00 am to 17:00 PM (1 Hour is break). Can you please help me.

        For Example – a case has come at 01/04/2015 15:30:00 and completed at 07/04/2015 10:37:00 (This includes the weekend and holiday of 03/04/015 & 06/04/2015), so the total time taken should be (If you consider the total 9 working hours for the day including the break timing) 13 hours & 7 Mins.

        Can we please get the above result.

        Thanks
        Prashant

      • hi

        i need 1 help how to subtract starting date to deliver date excluding Sunday and public holidays answer should be need in count

  • Hi… I replaced ’ with ‘ & applied the formulae. Now its coming correctly. Thks a lot…. Sorry to disturb you.

  • Hi,
    I have set of holidays in May’12 mentioned in Sheet A (01/05/2012, 06/05/2012 13/05/2012 20/05/2012, 27/05/2012)
    Start Date: 05/05/2012
    End Date: 14/05/2012
    Result: 8 days i.e. (Start Date-End Date) – (Only Sundays & special holidays should get substracted from Start to End date, if falls in this period).
    Please help with formula.
    Regards,
    Anil Sali

    • Hi,

      From the set of holiday dates you have mentioned, it looks like you want to exclude user defined dates (which are not Sundays) and Sundays. You seem to have assumed Saturday as a working day. In sheet A, mention all the non Sunday holidays. Among the dates mentioned by you, the only non Sunday holiday is 01/05/2012 i.e. 1 May 2012. Suppose 01/05/2012 is in cell A5 of the Holidays sheet, start date is in cell B5 of sheet1 and end date is in cell C5 of sheet1

      If you are using Excel 2010, enter the following formula in cell D5 of sheet1

      =NETWORKDAYS.INTL(B5,C5,11,Holidays!A5)

      If you are using Excel 2007 or prior versions, then enter the following formula in cell D5 of sheet1

      =IF(C5>B5,NETWORKDAYS(B5,C5,Holidays!A$5)+SUMPRODUCT(1*(WEEKDAY(ROW(INDIRECT(B5&":"&C5)),2)=6)),0)

      Hope this helps.

      • Hi Ashish,

        i am facing problem in case saturday is considered as working day but there is public holiday on a saturday. Now i want to exclude that saturday by entering in public holiday list. Please help.

  • Hi,
    Ashish thank you very much, finally after copy & paste formula, its working fine now. Thanks for your time and patience in understanding my problem and giving the solution.
    Regards, Anil Sali

  • Hi Ashish Mathur:
    I do not find any attached file within this email, could you please re-send it to me?
    I look forward to your reply
    Thanks you very much for any suggestions
    Kindly regards

    ‘————————————————————————————–

    Ashish Mathur commented on Calculate turn around time excluding Sundays and public holidays.
    in response to H. M. Talal:
    The following formula is not excluding holidays =IF(OR(WEEKDAY(INT(A2),2)>=6,COUNTIF(‘Public Holidays & work hours’!$B$2:$B$9,INT(Data!A2))=1),0,IF(‘Public Holidays & work hours’!B$14-MOD(Data!A2,1)=6,COUNTIF(‘Public Holidays & work hours’!$B$2:$B$9,INT(Data!B2))=1,INT(B2)=INT(A2)),0,MAX(MIN(MOD(B2,1),’Public Holidays & work hours’!$B$14),’Public Holidays & work hours’!$B$13)-’Public Holidays & work hours’!B$13)+IF(INT(B2)-INT(A2)< =1,0,(INT(B2)-INT(A2)-1)*('Public Holidays & work hours'!B$14-'Public Holidays & work hours'!B$13))-(IF(INT(B2)-INT(A2)=6)))*(‘Public Holidays & work hours’!B$14-’Public Holidays & work hours’!B$13))-(IF(INT(B2)-INT(A2)<=1,0,SUMPRODUCT(1*(ISNUMBER(MATCH(ROW(INDIRECT(INT(A2)+1&”:”&INT(B2)-1)),(‘Public Holidays & work hours’!$B$2:$B$9),0))))*(‘Public Holidays & […]

    Hi,
    I am not sure of what changes you have made to my formula but it is differently different from the one I have shown in cell D2 of the file attached in the original post. One small difference which I can observe is the bracketing in the formula.
    In cell D2 of the file in my original post, the value in cell D2 is 00:35:00 for a start time of 01/04/2012 09:12 and End time of 02/04/2012 08:35. Since 01/04/2012 was a Sunday, the hours on Sunday have been excluded.
    I suggest you use my formula in the attached file.

  • Hello,

    I want to jst find out the time between two dates excluding only weekends(saturday, sunday).

    Please help me

    Pravalika

  • HI Ashish,

    Thanks for your tips!

    Above example was in line with my request however it had public holidays and working hours classified.

    I figured a solution however it works only when we have non-weekends as start and end date. when the duration of the week increases (between the start date and end date) the below formulae doesnt help

    =(B2-A2-TRUNC(B2-A2-NETWORKDAYS(A2,B2)+1))*24

    where B2 is the end date with timestamp and A2 is start date with timestamp.

    Regards,
    Vinod Stevan A I

    • Hi,

      Please describe the business problem. What exactly are you trying to accomplish? Show your data layout with expected result. If possible, upload your somewhere and share the link of the uploaded file here.

  • Hi,

    Using your workbook example, I have my beginning dates in Column A and End Dates in Column B that are both in dd/mm/yyyy hh:mm format. I have the state/territory that each input came from in Column C. In the ‘Public Holidays & Work Hours’ worksheet, I have the public holidays categorised into each state and territory with an absolute value applied to each e.g. if the State/Territory in Column C is ‘QLD’, then the absolute value applied for QLD public holidays at the ‘Public Holidays & Work Hours’ worksheet is QLDHolidays.

    If I wanted to calculate the turnaround time excluding Saturdays, Sundays and the state/territory public holidays, how would I do this?

  • Hi Ashish,

    Thank you for your reply and taking the time to do this.

    Can you please delete all headings from the top row other than those being calculated on and then republish it to your share point? I forgot to do this before sending it to you.

    Regards,
    Lindsay_80

  • Hi Ashish,

    I have one final question about manipulating the formula for another use. Using Row 61 as an example, the request date is a public holiday so the business hour calculation would start on 8 October 2013. Based on this, the elapsed business hours would be 40 hours and 25 minutes as the status date is 25 minutes after the end of the business day. Is there a way to change the formula to calculate this also? I would use it elsewhere in the document.

    • Hi,

      That is exactly what is already happening in the formula. The result is cell G61 is 40 which is computed as 8 hours each for 5 days – October 8,9,10,11 and 14. Also, if the status date is 25 minutes after the cut off time, the 25 minutes have been ignored.

  • Hi Ashish,

    Thank you for your reply.

    Yes, the formula is working brilliantly for the scenario that I originally outlined. I was wondering though if I wanted to use the formula for another purpose elsewhere so that it included the 25 minutes, how would it need to be changed?

    Any thoughts that you have would be appreciated.

    Regards.

  • Hi,

    I need help to calculate downtime and conditions are

    1) Sunday should be excluded
    2) Working hours is 8:45 to 17:00
    3)After working hours downtime should not be considered.

    Please help with a formula and also explain the same

    • Hi,

      Upload your file to SkyDrive and share the link of the uploaded file here. In the file, very clearly shown the data and also show the expected solution for a few sample entries.

  • Hi,

    For the same problem in 2007, I had used below formula to calculate time in minutes. Just thought of sharing here. please give a look.

    =(((SUMPRODUCT(IF((WEEKDAY(ROW(INDIRECT(INT(A2)&”:”&INT(B2))),1)=6)+(WEEKDAY(ROW(INDIRECT(INT(A2)&”:”&INT(B2))),1)=7),0,1))-SUMPRODUCT(IF(IFERROR(MATCH(ROW(INDIRECT(INT(A2)&”:”&INT(B2))),Holiday,0),0),1,0)))*9)-(IF(INT(B2)=IFERROR(INDEX(Holiday,MATCH(INT(B2),Holiday,0)),0),0,((WEEKDAY(B2,1)<6)*MAX(0,MIN(9,17-MOD(B2,1)*24)))))-(IF(INT(A2)=IFERROR(INDEX(Holiday,MATCH(INT(A2),Holiday,0)),0),0,((WEEKDAY(A2,1) Start Date & Time
    B2–> End Date & Time
    Weekends –> Friday & Saturday
    Working Hours–> 8:00 – 17:00
    I had considerd lunch hours also in caluclation.
    Holiday –> Named range of public holidays in cell E2:E6 in my case.

  • Hi,

    I dont know the formula above doesnt appeared correctly. This is an Array formula so Ctrl+Shift+Enter is must

    =(((SUMPRODUCT(IF((WEEKDAY(ROW(INDIRECT(INT(A2)&”:”&INT(B2))),1)=6)+(WEEKDAY(ROW(INDIRECT(INT(A2)&”:”&INT(B2))),1)=7),0,1))-SUMPRODUCT(IF(IFERROR(MATCH(ROW(INDIRECT(INT(A2)&”:”&INT(B2))),Holiday,0),0),1,0)))*9)-(IF(INT(B2)=IFERROR(INDEX(Holiday,MATCH(INT(B2),Holiday,0)),0),0,((WEEKDAY(B2,1)<6)*MAX(0,MIN(9,17-MOD(B2,1)*24)))))-(IF(INT(A2)=IFERROR(INDEX(Holiday,MATCH(INT(A2),Holiday,0)),0),0,((WEEKDAY(A2,1)<6)*MAX(0,MIN(9,MOD(A2,1)*24-8))))))*60

    Thanks.

  • What if you wanted to calculate the working hours to include Saturday and Sunday as working hours, but exclude any day declared as a public holiday? Is that possible?

  • Let me add one more thing to that, I’m currently using your formula that excludes Sunday and non-Sunday-Holidays – many thanks for that, it’s helped tremendously. Thing is, I’d like to build data models to take advantage of PowerPivot and SharePoint 2013. To use data models requires a 2013 Excel format, but for some reason I can only save in 97-2003 format with your formula that excludes Sunday’s and non-Sunday-Holidays. Do you happen to have a 2013-friendly version?

  • Ashish, I am unable to get your single cell formula to work for the following examples for counting the number of working days

    Example 1
    Start Date/Time: Cell A2 = July 3, 2014 09:30:00 AM
    Finish Date/Time: Cell B2 = July 5, 2014 12:00:00 PM

    Example 2
    Start Date/Time: Cell A3 = July 6, 2014 06:00:00 PM
    Finish Date/Time: Cell B3 = July 6, 2014 08:00:00 PM

    Holiday to Exclude July 4, 2014. Working hours to count would only be 8:00:00 AM to 5:00:00 PM.

    Can you please provide a formula.

    Thanks.

    Mike

    • Hi,

      In Example 2, the result should be 0 because the start and End time are both after Work hours. For Example 1, I get the result as 11.5 hours. Please review my solution in this workbook.

      Hope this helps.

  • Hi Ashis
    thank you for your excellent formula, I tried it and it is working perfect except in one case
    so I have the following update on it

    I found a case if we put in the sheet
    “mail Date”: 4/2/2012 7:12:00 AM
    and “Creation date”: 4/2/2012 7:20:00 AM
    it will calculate the “Turn around time (single cell formula)” = -0.027777778
    which is wrong cause the “Mail time” and “Creation time” are before the “Work start time” and it should be 0

    so I changed a small part of the formula from
    B2
    to be
    (INT(B2)+MAX(MOD(B2,1),’Public Holidays & work hours’!B$13))

    which solved the issue and didn’t harm the other cases at all 🙂

    and the complete formula became

    =IF(OR(WEEKDAY(INT(A2),2)=7,COUNTIF(‘Public Holidays & work hours’!$B$2:$B$9,INT(Data!A2))=1),0,IF(‘Public Holidays & work hours’!B$14-MOD(Data!A2,1)<0,0,MIN('Public Holidays & work hours'!B$14-MAX(MOD(Data!A2,1),'Public Holidays & work hours'!$B$13),
    (INT(B2)+MAX(MOD(B2,1),'Public Holidays & work hours'!B$13))
    -MAX(A2,INT(A2)+'Public Holidays & work hours'!$B$13))))+IF(OR(WEEKDAY(INT(B2),2)=7,COUNTIF('Public Holidays & work hours'!$B$2:$B$9,INT(Data!B2))=1,INT(B2)=INT(A2)),0,MAX(MIN(MOD(B2,1),'Public Holidays & work hours'!$B$14),'Public Holidays & work hours'!$B$13)-'Public Holidays & work hours'!B$13)+IF(INT(B2)-INT(A2)<=1,0,(INT(B2)-INT(A2)-1)*('Public Holidays & work hours'!B$14-'Public Holidays & work hours'!B$13))-IF(INT(B2)-INT(A2)<=1,0,SUMPRODUCT(1*(WEEKDAY(ROW(INDIRECT(INT(A2)+1&":"&INT(B2)-1)),2)=7)))*('Public Holidays & work hours'!B$14-'Public Holidays & work hours'!B$13)-IF(INT(B2)-INT(A2)<=1,0,SUMPRODUCT(1*(ISNUMBER(MATCH(ROW(INDIRECT(INT(A2)+1&":"&INT(B2)-1)),('Public Holidays & work hours'!$B$2:$B$9),0))))*('Public Holidays & work hours'!B$14-'Public Holidays & work hours'!B$13))

    Hope it will be for benefit of the others

    B.R
    Medhat Helmy

  • Notified time (mm/dd/yy hh:mm:ss) 2008/01/16 10:00:00
    Close Time (mm/dd/yy hh:mm:ss) 2008/01/17 14:00:00

    i TIRED ALL THE FORMULAS
    1. =(NETWORKDAYS(D2,E2)*8)-IF(WEEKDAY(D2,2)>=6,0,MIN(MAX(24*(MOD(D2,1)-K2),0),8)) – IF(WEEKDAY(E2,2)>=6,0,MIN(24*MAX((L2-MOD(E2,1)),0),8))
    2. =((((((NETWORKDAYS(D2;E2))-1)*9)+(IF(HOUR(D2)<HOUR(E2);HOUR(E2)-HOUR(D2);-(HOUR(D2)-HOUR(E2)))))*60)+(IF(MINUTE(D2)<MINUTE(E2);MINUTE(E2)-MINUTE(D2);(60-(MINUTE(D2)-MINUTE(E2)))-60)))/1440
    3.
    =IF(NETWORKDAYS(D2,E2)=1,(MIN(K2,MOD(E2,1))-MAX(L2,MOD(D2,1))),(NETWORKDAYS(D2,E2)-2)*(600/1440)+(K2-MAX(MOD($D$2,1),L2))+MIN(MOD($E$2,1),L2)-K2)

    i TRIED ALL THE FORMULA CAN ANYONE GIVE ME SIMPLE FORMULA.

    I NEED TO CALCULATE THE TO TAL TIME TAKEN FOR THE TICKET TO BE RESOLVED ASSUMING THAT SATURDAY AND SUNDAY IS A HOLIDAY AND WE HAVE ONLY 9 WORKING HOURS THAT IS MORNING 9AM TO 6PM.

    i AM STRUGGLING SINCE QUITE A LONG TOME AND NO HELP:-(

  • Hi,

    I have an issue where the notified time in the tool can be morning 2:00AM also but my shift timing starts from 9:00AM only till 6:00PM. Now how will this formula calculate?
    As per the formula the tiurn around time is negative or Zero but is there a way where i can specify a condition like if any ticket comes after 6:00PM and before 9:00Am the system should by default take it as 9:00AM logged for another morning.
    Eg
    Notified time: 8/1/2013 7:37:54 AM
    Closed time: 8/1/2013 8:01:40 AM
    Notified time system should consider: 8/1/2013 9:00:00 AM
    Closed time system should consider: subtraction of Closed time-Notified time but after “Notified time system should consider”

    • Hi,

      In the example which you have taken, what result would you expect. I understand that you want the Revised Notified time to be 8/1/2013 7:37:54 AM but then what should the closed time be?

      Please clarify.

      • Hi,

        Any ticket that is being logged into the system after the working Hours(9:00AM to 6:00PM) for that ticket the Revised Notified time should change to Next day Morning 9:00AM.
        Eg:
        Current Notified Time: 8/1/2013 7:37:54 AM
        Current Closed Time: 8/1/2013 8:01:40 AM
        Like in this,the ticket had come into the system at 8/1/2013 7:37:54 AM and was closed at Current Time: 8/1/2013 8:01:40 AM but i want that the revised Notified time should be 8/1/2013 9:00:00 AM and Revised Closed time should be the difference in actual time taken to close the ticket(Current Closed Time-Current Notified Time) and this time should be added to the Revised Notified time, like here in this example it took (8/1/2013 8:01:40 AM-8/1/2013 7:37:54 AM) excluding the dates suppose i get “X” hour/mint/sec then my Revised closed time should be (Revised Notified time+X) like in eg here (8/1/2013 9:00:00 AM+ X(Time)).

        I hope this will make my question clear. I have been stuck up on this since last 3 days.:-(

        Thanks In Advance for your help.

        – See more at: https://www.ashishmathur.com/calculate-turn-around-time-excluding-sundays-and-public-holidays/#comment-196490

        • Hi,

          I wish to seek a clarification. With the following data, could you tell me what value of “X” (as alluded by you above) would you want to see?

          Current Notified Time – 2 May 2014 6:55 AM (Friday)
          Current Closed Time – 6 May 2014 20:30 (Tuesday)

          What value of “X” would you want to see with the data above.

  • Thank you, Ashish! I’ve been working on this calculation for several days and was relieved to find your solution. It works perfectly.

  • Hi Ashish,

    Great to see the solution provided by you.

    I have similar question.

    TAT Hours excluding weekends and public holidays.

    Adding to it For eg: While case is log sometime we reject back to customer for a query and it takes bit time to get the response.

    SFDC tool gets the total time spent in pending customer que which includes all weekends and public holidays.

    But we need to exclude that in our Actual TAT.

    Could you please help.

  • Hi Ashish,

    I have attached the data. Where my Start time is A2 and End Time is A3 and keeps moving to next cell.

    I have holiday list for each group in Holiday Tab and Shift timings in L and M.

    Could you please help me in getting the TAT Hours excluding the Holidays listed based in groups and Non Business hours.

    Also can help me in getting the TAT Hours in hh.mm format as i am getting decimal value.
    https://drive.google.com/file/d/0B4yz2BKtccnxZE1TS3hoTTROeWc/edit?usp=sharing

  • Hi Ashish,

    https://drive.google.com/file/d/0B4yz2BKtccnxSnZXdkduSzNDUmc/edit?usp=sharing

    I have uploaded only specific data now.

    For Cell N2:
    A2: Start Time
    A3: End Time
    K2: Group – Each group will have different holidays which is in Tab Holidays
    L2 & M2 – Business Hours

    N2: Needs Buiness Hours excluding Holidays( Based on Group) and Weekends
    in hh:mm
    For Cell N3:
    A3: Start Time
    A4 : End Time
    K3: Group – Each group will have different holidays which is in Tab Holidays
    L3 & M3 – Business Hours

    N3: Needs Buiness Hours excluding Holidays( Based on Group) and Weekends

    Please let me know if it is clear.

    Thank you for the help.

  • Hi Ashish,

    This formula is precisely what I need. However my only variation is that Saturday working hours differ from the rest of the week i.e. it is 8am-6pm on weekdays and 8am-4pm on Saturdays. Is it possible to adapt the formula to calculate business hours based on this?

    I’m using the formula in D2 of your workbook, and it looks like this in my workbook:

    =IF(M2=””,””,HOUR(IF(OR(WEEKDAY(INT(H2),2)=7,COUNTIF(‘Public Holidays’!$B$2:$B$20,INT(Data!H2))=1),0,IF(‘Public Holidays’!E$3-MOD(Data!H2,1)<0,0,MIN('Public Holidays'!E$3-MAX(MOD(Data!H2,1),'Public Holidays'!$E$2),M2-MAX(H2,INT(H2)+'Public Holidays'!$E$2))))+IF(OR(WEEKDAY(INT(M2),2)=7,COUNTIF('Public Holidays'!$B$2:$B$20,INT(Data!M2))=1,INT(M2)=INT(H2)),0,MAX(MIN(MOD(M2,1),'Public Holidays'!$E$3),'Public Holidays'!$E$2)-'Public Holidays'!E$2)+IF(INT(M2)-INT(H2)<=1,0,(INT(M2)-INT(H2)-1)*('Public Holidays'!E$3-'Public Holidays'!E$2))-IF(INT(M2)-INT(H2)<=1,0,SUMPRODUCT(1*(WEEKDAY(ROW(INDIRECT(INT(H2)+1&":"&INT(M2)-1)),2)=7)))*('Public Holidays'!E$3-'Public Holidays'!E$2)-IF(INT(M2)-INT(H2)<=1,0,SUMPRODUCT(1*(ISNUMBER(MATCH(ROW(INDIRECT(INT(H2)+1&":"&INT(M2)-1)),('Public Holidays'!$B$2:$B$20),0))))*('Public Holidays'!E$3-'Public Holidays'!E$2))))

      • Thanks Ashish. Have got this working however if the creation date is during a Saturday the value in G2 is zero. When the mail and creation dates are either side of a Saturday (e.g. Friday and Monday) the G2 value is 8. Is there a way to include the Saturday hours if the creation date lies part way through the day?

        • You are welcome. The formula in cell G2 should be:

          =IF(INT(B2)-INT(A2)<=1,0,SUMPRODUCT(1*(WEEKDAY(ROW(INDIRECT(INT(A2)+1&":"&INT(B2)-1)),2)=6)))*('Public Holidays & work hours'!B$18-'Public Holidays & work hours'!B$17)+IF(WEEKDAY(B2,2)=6,MOD(B2,1)-'Public Holidays & work hours'!$B$17,0)+IF(WEEKDAY(A2,2)=6,'Public Holidays & work hours'!$B$18-MOD(Data!A2,1),0) Hope this helps.

  • Hi Ashish,

    Hopefully you can still assist with these formulas.

    Im looking to work out the time open for an Incident, regardless of when it is received (weekday, Saturday, public holiday etc) but with working hours of:

    Weekdays – 08:00 t0 17:00
    Saturdays – 08:00 to 12:00

    I want to exclude all Sunday’s and Public Holidays.

    It needs to cater for incidents logged on anyday though, as we do received backdated incidents that may have been logged during the week on the customer side, but we only received it over the weekend or public holiday.

    Please can you assist me?

      • Hi,

        So our working hours are:

        Weekdays – 08:00 to 17:00
        Saturdays – 08:00 to 12:00

        So we have a 9 hour working day on weekdays and a 4 hour working day on Saturday.

        Example:
        Incident received: 02-05-2015 07:15 (Saturday)
        Incident resolved: 02-05-2015 13:15 (same day)
        Expected result: 4:00

        Example:
        Incident received: 04-05-2015 10:00
        It is resolved: 06-05-2015 16:35
        Expected result: 24:35

        Example:
        Incident received: 05-05-2015 21:30
        Incident resolved: 06-05-2015 16:30
        Expected result: 8:30

        Example:
        Incident received: 08-05-2015 10:00
        Incident resolved: 11-05-2015 09:00
        Expected result: 12:00

        Hope this helps, and thank you.

  • Can I get a formula to calculate the time duration for a same sort of question.

    My actual query is…

    Cell A1 is start date & time >> ” 5/10/2015 4:00″
    Cell B1 is end date & time >> “5/10/2015 9:00″

    And I’m having a peak hour time duration is 8:00 AM to 10:00 AM”

    Now in Cell C1, I need “On Peak duration” which is “1:00” and in Cell D1, I need “Off Peak duration” which is “4:00”

    Here my total time duration is getting 5 hrs. which is having having 1 hr. in on-peak time and the rest four hours in off-peak time.

    Hope my query is clear

    Thanks,
    Suresh R

    • Hi,

      Try this

      1. In cell E1, enter this formula =B1-A1 and format as [hh]:mm
      2. In cell D1, enter this formula and format as [hh]:mm

      =IF(MOD(B1,1)TIME(8,0,0),0,TIME(8,0,0)-MOD(A1,1))

      3. In cell C1, enter this formula =E1-D1 and format as [hh]:mm

      Hope this helps.

      • Its not resulting the correct output. Guess there is some operator symbol is missing in the logical test of If formula. Can you please check.

          • Hi… I applied the formula and changed the peak hour duration ie. “7AM to 9PM” but I didn’t get the correct result.

            Eg:

            A1 = 5/9/2015 2:00:00 PM
            B1 = 5/10/2015 12:00:00 PM
            C1 =E1-D1
            D1 =IF(MOD(B1,1)<TIME(21,0,0),0,TIME(7,0,0)-MOD(A1,1))
            E1 =B1-A1

            I'm getting C1=22:00 D1=0:00 E1=22:00 but it should have to result the following
            C1=12:00 D1=10:00 E1=22:00

            Please advise.

            Thanks

          • Hi,

            Again I’m getting error. Please refer the below table

            Time in Time out Peak Off peak Total
            5/9/2015 14:00 5/9/2015 15:00 00 15:00 ###### 00 1:00
            5/9/2015 14:00 5/10/2015 22:00 00 15:00 00 17:00 01 8:00

            Answer should be as follows

            Peak Off peak
            00 01:00 00 00:00
            00 21:00 00 11:00

          • Hi Ashish,

            Sorry to bother you. If I try with outage time before 7AM its returning negative hours.

            Start time = 5/10/2015 4:00
            End time = 5/10/2015 5:00

            Can you please check this.

            Thanks

  • Hi Ashish,
    I am new to this website. I found the discussion very interesting..
    In fact, I am looking for a formula to calculate the TAT between 2 dates and times excluding WE and public holidays.
    I have one additional criteria. All requests coming in after 7 pm should be counted as a next day request. Once again here, there should be an offset if the next day is a WE or a ppublic holiday.
    The normal business hours are 8:00 am – 5:00 pm.
    Thank you in advance for your help.
    I’ll really appreciate that.

    Shameem

  • Hi Ashish, Thank so much for the reply.

    Will check it soon and will let you know.

    What is “Mail date” and “Creation date”?

    For the TAT, I think we have to find the date/time when the mail enters the mail box and the date/time when employee has finished processing it.

    Really appreciate it.
    Shameem

  • Hi Ashish,

    I checked the workbook. If I’m not wrong, the Mail date is the date/time when the mail enters the mail box and the creation date is the date/time when employee has finished processing it. If this is the case, then, yes, the formula is perfect. I also checked for request coming in after 5 pm, that’s working fine also.

    I have another request: what about if Mail date and Creation date have both the dates and times separated? Mail date in 1 column and Mail time in another. Same for Creation date.
    Thanks again for your precious help.

  • Hi Ashish,
    When inserting the Mail and Creation dates below, I have a TAT of 14:30. Is that correct? Thanks a lot again Ashish,

    Mail date Creation date Turn around time
    07/05/2015 08:30 17/05/2015 08:40 14:30:00

      • Thanks for this update Ashish…
        Sorry to bother you, I can’t understand the 62 hours while the result is 14 hours and 30 mins.

        Thanks for your precious help again.

          • Ashish,
            As discussed earlier, you asked the question:
            So anytime after 7 PM will be considered on the next business day. Am I correct? I replied “Yes”.
            Reminder – Business hours are from 10 am to 7 pm.
            However, I forgot to tell you that all requests coming after 6:30 pm are considered as next day requests.
            So do you think that I just need to adjust the work end time on the “Public Holidays & work hours” sheet to 6:30 pm? Or will that be an issue?
            Thanks again a lot for your precious help.

  • I Ashish, hope you good !!, I want to calculate TAT, I have data in the following format, Work timings are 08:00 to 20:00 and SAT and Sun are off. I have also have a list of Holidays Z2:Z8

    Date Req :A2,
    Time Req : B2

    Date Completed: M2
    Time Completed: N2

    Can you help me with the formula, My Math is not very good so I am finding it difficult to do on my own

      • Hello Ashish,

        Thanks a lot for your help !! however, I do have one query, formula seems to be working fine other than the 1st record where it is showing a gap of 12Hrs between start date and end date, which should be “0”, as start date is 30th of May which was Saturday, hence, holiday and end date and time is 1st of June 09:25. So ideally TAT should only be 1:25 Hrs, whereas it is calculating 13:25.
        I am not sure why only in cell one it is calculating incorrectly and rest seems to be working fine.

        Start
        30-05-2015 01:46

        End
        01-06-2015 09:25

        Turn around time (using formulas from multiple columns)
        13:25:00 (It should be only 1:25)

        Hours worked on start date
        00:00:00

        Hours worked on End date
        01:25:00

        Total hours between start and end date (=IF(INT(F4)-INT(E4)<=1,0,(INT(F4)-INT(E4)-1)*('Public Holidays & work hours'!L$2-'Public Holidays & work hours'!L$1)))

        12:00:00

        Public holiday hours
        00:00:00

        Thanks for your help !!

  • Hi Ashish,

    I am having an issue with some data for the TAT calculation.

    Below data for which TAT is not calculated. Showing ######## in result cell.

    Request received date and time = 04/06/2015 00:41:00

    Order Completed date and time = 04/06/2015 14:15:00

    Another example:
    Request received date and time = 04/06/2015 06:03:00

    Order Completed date and time = 04/06/2015 14:09:00

    Thanks a lot as usual Ashish…

    • Hi,

      I do not face any errors at all. The answer for the first set is 04:15 and for the second one is 04:09. I have assumed working hours as 10:00 AM to 06:30 PM. Upload the workbook to OneDrive and show me the problem which you are facing.

    • What should I see in that link. Atleast tell me where the problem is. In which cell do you have my formula? Show the formula and also the error. Think before you post.

      • Sorry for that Ashish,

        I am having an issue with some data for the TAT calculation.

        By clicking on this link – https://onedrive.live.com/redir?resid=873CA23F32B777A7!112&authkey=!AHflItbvzHRmaAk&ithint=file%2cxlsx

        You will see that in column J I am having ######## as result.

        However, this is only the case for some lines. So I think that below formula is working fine.

        The formula used is:
        =IF(OR(WEEKDAY(INT(E274);2)>=6;COUNTIF(‘Public Holidays & work hours’!$B$2:$B$9;INT(Raw_Data_Productivity_Tracker!E274))=1);0;IF(‘Public Holidays & work hours’!B$14-MOD(Raw_Data_Productivity_Tracker!E274;1)=6;COUNTIF(‘Public Holidays & work hours’!$B$2:$B$9;INT(Raw_Data_Productivity_Tracker!E274))=1;INT(J274)=INT(E274));0;MAX(MIN(MOD(J274;1);’Public Holidays & work hours’!$B$14);’Public Holidays & work hours’!$B$13)-‘Public Holidays & work hours’!B$13)+IF(INT(J274)-INT(E274)<=1;0;(INT(J274)-INT(E274)-1)*('Public Holidays & work hours'!B$14-'Public Holidays & work hours'!B$13))-IF(INT(J274)-INT(E274)=6)))*(‘Public Holidays & work hours’!B$14-‘Public Holidays & work hours’!B$13)-IF(INT(J274)-INT(E274)<=1;0;SUMPRODUCT(1*(ISNUMBER(MATCH(ROW(INDIRECT(INT(E274)+1&":"&INT(J274)-1));('Public Holidays & work hours'!$B$2:$B$9);0))))*('Public Holidays & work hours'!B$14-'Public Holidays & work hours'!B$13))

        Can you please help me out there.

        Thanks for your precious help.

        • You once again have not bothered to review your very own file. First of all, there is no sheet there named Public Holidays & work hours or Raw_Data_Productivity_Tracker. Secondly, column J does not have any formula – it has hard coded #### values.

          Upload the file where your actual worksheets are, your formulas have been written. I will just correct for the mistake in your formula. Now please review properly before you post your question.

  • Ashish,
    Thanks for the updated response.

    However, if I put the following:
    Mail date Creation date TAT
    5/29/15 12:07 6/1/15 19:10 21:40

    TAT is 21:40 hours which is incorrect. I have the impression that it is taking into consideration Saturdays.

    Can you please help?

        • Hi Ashish,
          I have a new TAT being defined.

          All the requests received on Day D and treated on the same day will be considered as Same Day (even if treatment is done until 11:30 pm).
          All requests received before 11:00 pm and processed next day (D+1) until 11:30 pm will be considered as Next Day.
          All requests received before 11:00 pm and processed the day after (D+2) or later will be considered as Greater Than 2 Days.

          All requests received after 11:00 pm and processed same day (before 11:30 pm) is considered as Same Day.
          All requests received after 11:00 pm and processed next day (D+1) until 11:30 pm is considered as Same Day.
          All requests received after 11:00 pm and processed the day after (D+2) will be considered as Next Day.
          All requests received after 11:00 pm and processed after D+2 will be considered as Greater Than 2 Days.

          The goal is to calculate the % Same Day, Next Day and Greater Than 2 Days.

          I would like to know if you will be able to help me on that. Thanks a lot Ashish as usual. You are helping me a lot here.

          Work starts at 2:30 pm and ends at 11:30 pm.
          As usual, Saturdays, Sundays and public holidays should not be taken into account

  • Hi Ashish,
    Working fine only for some dates during the weekend.
    Please find uploaded file “Weekly Report-15 May to 26 June.xlsx”.
    Link is : http://1drv.ms/1eV8jt3
    As you can see on “Raw_Data_Productivity_Tracker” tab for “Associate Name” = “Nadeem”, the TAT in column M should have been “Same day” instead of “Greater than 2 Days” as per TAT rules on sheet “Public Holidays & work hours”.

    Thanks for your help as usual.

    Shameem

  • Hi Ashish,
    Still an issue…

    Please have a look on this link: http://1drv.ms/1GQ5kbg

    The TAT in column M should have been “Next Day” as per the TAT defined below : (It’s not a new one, the same as before)…

    All the requests received on Day D and treated on the same day will be considered as Same Day (even if treatment is done until 11:30 pm).
    All requests received before 11:00 pm and processed next day (D+1) until 11:30 pm will be considered as Next Day.
    All requests received before 11:00 pm and processed the day after (D+2) or later will be considered as Greater Than 2 Days.

    All requests received after 11:00 pm and processed same day (before 11:30 pm) is considered as Same Day.
    All requests received after 11:00 pm and processed next day (D+1) until 11:30 pm is considered as Same Day.
    All requests received after 11:00 pm and processed the day after (D+2) will be considered as Next Day.
    All requests received after 11:00 pm and processed after D+2 will be considered as Greater Than 2 Days.

    Work starts at 2:30 pm and ends at 11:30 pm.
    As usual, Saturdays, Sundays and public holidays should not be taken into account

    • Hi,

      Try this formula in column M

      =IF(OR(INT(E706)=INT(J706),AND(NETWORKDAYS(INT(E706),INT(J706),Table1[Date])-2=0,MOD(J706,1)<='Public Holidays & work hours'!$B$14,MOD(E706,1)>=’Public Holidays & work hours’!$B$14-TIME(0,30,0)),AND(NETWORKDAYS(INT(E706),INT(J706),Table1[Date])-2<0,MOD(J706,1)<='Public Holidays & work hours'!$B$14)),"Same Day",IF(NETWORKDAYS(INT(E706),INT(J706),Table1[Date])-2<=1,IF(OR(AND(MOD(J706,1)<='Public Holidays & work hours'!$B$14,MOD(E706,1)<='Public Holidays & work hours'!$B$14-TIME(0,30,0),NETWORKDAYS(INT(E706),INT(J706),Table1[Date])-2=0),MOD(E706,1)>‘Public Holidays & work hours’!$B$14-TIME(0,30,0)),”Next Day”,”Greater than 2 Days”),”Greater than 2 Days”))

      Hope this helps.

  • I HAVE A QUERY ON WORKING THE TAT FORMULA

    DEBIT CARD ACTIVATED ON 10TH DEC BUT I GOT THE FILE ON 14TH DEC, I WANT TO FIND THE TAT EXCUDING WEEKENDS N PUBLIC HOLIDAYS

  • HI Ashish Ji,

    I badly need your help. I need a workbook to calculate the TAT where input is order confirm date & time and Order Complete Date & Time.
    Now orders recieved till 17:00 delivered same day maximum by 22:00 (So need tat calulation as it is i.e. 22:00-17:00 but work for order processing is hrs are 9:30 to 18:30)

    Order confirmed after 17:00 will be delivered next day so for customers TAT starts from 17:00 to 18:30 same Day & 9:30 to Completion Time)
    :
    This is where I am Stuck because your workbook is calculating TAT till 18:30

    TAT is Complete Time – Confirm Time

    Order Taking Hrs : 9:30 to 18:30 but deliver upto 22:00

    Weekly Off will be there (No off on Saturday, Sunday)

    Please crack this problem for me.

    Thanking You.

    Vikas

      • I am using your worksheet turn-around-time v1 with below mentioned formula for TAT calculation, which is working great if shift timing is from 9:30 to 18:30.

        =IF(COUNTIF(‘Public Holidays & work hours’!$B$2:$B$52,INT(‘Real Time TAT’!G3))=1,0,IF(‘Public Holidays & work hours’!B$57-MOD(‘Real Time TAT’!G3,1)<0,0,MIN('Public Holidays & work hours'!B$57-MAX(MOD('Real Time TAT'!G3,1),'Public Holidays & work hours'!$B$56),H3-MAX(G3,INT(G3)+'Public Holidays & work hours'!$B$56))))+IF(OR(COUNTIF('Public Holidays & work hours'!$B$2:$B$52,INT('Real Time TAT'!H3))=1,INT(H3)=INT(G3)),0,MAX(MIN(MOD(H3,1),'Public Holidays & work hours'!$B$57),'Public Holidays & work hours'!$B$56)-'Public Holidays & work hours'!B$56)+IF(INT(H3)-INT(G3)<=1,0,(INT(H3)-INT(G3)-1)*('Public Holidays & work hours'!B$57-'Public Holidays & work hours'!B$56))-IF(INT(H3)-INT(G3)<=1,0,SUMPRODUCT(1*(ISNUMBER(MATCH(ROW(INDIRECT(INT(G3)+1&":"&INT(H3)-1)),('Public Holidays & work hours'!$B$2:$B$52),0))))*('Public Holidays & work hours'!B$57-'Public Holidays & work hours'!B$56))

        Now thing is this workbook calculate TAT till 18:30 & carried forward next day.

        Please understand the below mentioned condition

        1. Order recieved between 16:00 to 18:30 delivered next day but TAT calculation start from {(confirm time to shift end time same day) + (Shift start time to complete time next Day)} – I am getting this TAT from workbook provided by in blog. Moreover I am getting exact TAT for all the time scenarios, except below scenario.

        Order recieved till 16:00, I deliver same day till 22:00 in the night,

        IF I recieved order before 16:00 & deliver it by 22:00, this workbook will not calculate TAT till 22:00 but till 18:30.

        For Example
        Confirm Time : 15:42
        Complete Time : 21:30

        Processing Shift Start : 9:30
        Processing Shift End : 6:30
        Last Delivery Slot : Till 22:00
        Weekly OFF : Not Fixed

        FOr refrence : https://drive.google.com/open?id=0B-l4SSdyBOmATzdJT1FrVmZNR1U

        • Hi,

          That link asks me to request access so I cannot download the workbook. Furthermore, are you saying that the End time is flexible i.e. if the order is received before 16:00, then the End time should be till 22:00 and if the order is received after that, then the End till should remain 18:30? Please clarify.

          • Hi,

            Thank you for your patience and sorry for the delay in replying. I think I have got the correct answer. Please refer to the result in column I of the “Real Time TAT” worksheet. I have modified my formula in column S only. Please check the result thoroughly and let me know of fallacies you identify. My guess is that when you test with other entries as well, you will get incorrect answers because I have not modified the formula in columns T:V. Let me know of those cases.

            My suggestion is that for the time being, do not bother about getting the one cell fancy answer i.e. ignore column J. Once we are convinced that the individual components of the solution i.e. columns S:V are all working well, we can roll all those individual cells into one.

            Please download the workbook from here.

  • Hello Ashish,

    Just need some help on same as the above where I am trying to compute the TAT of our email handling but the situation is this:

    Email Date Received: 5/12/2016 11:58:02 AM
    Email Addressed: 5/15/2016 10:05:45 AM
    Weekdays: Sunday to Thursday
    Weekends: Friday & Saturday
    Office Hours: 10:00am to 6:00pm

    I cannot locate a formula to customize the weekend data to exclude Friday and Saturday. Hoping for your assistance.

  • Hi,
    I have a problem to work out on the formulae to calculate the TAT with condition excluding weekend and public holiday (please refer to the details below) and our company’s working is starting Monday to Thursday from 8.45am to 5.45pm and Friday from 8.45am to 4.45pm.

    Greatly appreciate if you can help me with the calculation.

    Various Scenarios Task Received Task Completed Actual
    Result
    calculated
    manually
    (in hrs)

    pub hol 22/01/2016 09:00 AM 26/01/2016 11:00 AM 9
    22/01/2016 10:00 AM 22/01/2016 12:00 PM 2
    2nd day 14/01/2016 10:00 AM 15/01/2016 12:00 PM 11
    pub hol 05/02/2016 03:00 PM 10/02/2016 10:00 AM 3
    22/01/2016 10:00 AM 22/01/2016 11:00 AM 2
    weekend 19/02/2016 10:00 AM 22/02/2016 09:00 AM 7

    17/07/15 Raya
    31/08/15 Merdeka
    16/09/15 M’sia Day
    24/09/15 Raya Haji
    14/10/15 Muharram
    10/11/15 Deepavali
    24/12/15
    25/12/15
    01/01/16
    25/01/16
    08/02/16
    09/02/16
    02/05/16 Replacement of Labour Day

    Thanking you in advance.

    regards
    queenie

    • Hi,

      The data has not been pasted properly. Upload the workbook to OneDrive and share the download link here. In the workbook, please also clearly show your expected answers.

  • Sorry Ashish,
    my office network is not allowed us to access OneDrive. i have copy the data again and looks clearer and hope if it is fine with you. Thank you once again.

    Task Received Task Completed Expected Result
    (in hrs)
    Date Time Date Time
    22/01/2016 09:00 AM 26/01/2016 11:00 AM 9
    22/01/2016 10:00 AM 22/01/2016 12:00 PM 2
    14/01/2016 10:00 AM 15/01/2016 12:00 PM 11
    05/02/2016 03:00 PM 10/02/2016 10:00 AM 3
    22/01/2016 10:00 AM 22/01/2016 11:00 AM 2
    19/02/2016 10:00 AM 22/02/2016 09:00 AM 7

    Public Holiday
    17/07/15
    31/08/15
    16/09/15
    24/09/15
    14/10/15
    10/11/15
    24/12/15
    25/12/15
    01/01/16
    25/01/16
    08/02/16
    09/02/16
    02/05/16

    regards
    queenie

  • Hi I have to calculate TAT excluding the holiday and saturday sunday too. working days and TAT Time

    Open Date OpenTime” closed DT Closed Time TAT Hrs TAT MTs
    01-Jun-1 9.58 7-Jun-16 14.36 – –

    TAT No. Working days

    • Hi,

      Download the workbook from the link shared in the Original Blog article. In cell D2 of the Data worksheet, replace all =7 with >=6. Modify data on the “Public Holidays & work hours” worksheet.

      Hope this helps.

    • Hi,

      Please share more details. Which are your Holidays? Please also upload your workbook to some file hosting service and share the download link here. In that workbook, please also show your expected result.

  • Hello Ashish,

    i have scrolled through several posts but i am not yet where i want to be.
    i am not sure if you can assist me on my business case without my sheet but i cant upload to skydrive or onedrive because of sensitive information.

    i need to calculate hours excluding the weekend, and below formula works when the start or end date is in the weekend.

    =IF(OR([@EndDateWeekday]=”Sun”,[@EndDateWeekday]=”Sat”),IF([@StartDate]>[@EndDate],0,(NETWORKDAYS([@StartDate],[@EndDate])-NETWORKDAYS([@StartDate],[@EndDate])*MOD([@StartDate],1)-NETWORKDAYS([@EndDate],[@EndDate])*(1-MOD([@EndDate],1)))*1),(NETWORKDAYS(IF(WEEKDAY([@StartDate],2)>5,WORKDAY([@StartDate],1)+1/3,[@StartDate]),[@EndDate])-1+MOD([@EndDate],1)-MOD(IF(WEEKDAY([@StartDate],2)>5,[@StartDate],WORKDAY([@StartDate],1)+0/3),1))*1)

    However, when the start date friday 2:05 and the end date is saturday 10:55
    it calculates correctly to Saturday 00:00 which means 21:55.
    But when the start date is Thursday it starts calculating from Thursday 02:05 to Friday 00:00 and Friday 2:05 to Saturday 00:00 which means 21:55 times 2 = 43:50 that is not correct as it should only add another 24 hours when the date is on thursday.

    I think it has something to do with the *MOD([@StartDate],1) but i cant seem to find a solution, can you create a workbook for me?

    Many thanks and regards,

  • Hello ashish ji,

    I have same updated in the microsoft community. Please help me on this issue.
    Link : https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-msoffice_custom-mso_2016/incident-estimated-resolve-date-and-time/9714f2b6-2ec8-4acf-9803-6c416a9c9b46?tm=1519797340383

    My Question description:

    I want formula for incident ticket expected resolve date and time. I have data there is mentioned ticket number and submit date and time so I need within which date and time ticket will be resolve. Some important info. Shift time is 7.00 am to 10.00 pm and friday 9.30 am to 6.30 pm . And all days working no holiday and no weekend so please help on this . Thanks in advance. Turn around time is :72 hours

    Regards,
    Abhishek shrivastav

  • Hi,

    I need a formula to calculate TAT for ticketing system. Details as below

    Working hours – Start 8.30 am & End Time 2.30 am
    Exclude – Saturdays & Sundays & holidays
    TAT based on category (A, B & C) – 2, 4 & 8 hrs respectively

    • Hi,

      I do not understand the meaning of “TAT based on category (A, B & C) – 2, 4 & 8 hrs respectively”. Please try to apply the solution which I have shared in the Blog. If it does not work as per your expectation, then post back.

  • Hi Ashish,
    I was looking for an excel formula for SLA Calculation. After exhaustive search, found your page. I found the formula needed for my Calculation while going through the conversations.

    I must say you are doing an awesome job. This note is to convey my heartfelt thanks.

    Regards,
    Parth

  • Dear Ashish,thanks for the great support and amazing solutions.
    Request a help to resolve below question. instead of Sunday , the weekends are Firaday and Saturday.

    Question:
    Data in column A and B show beginnig and ending date/time stamps. On the next sheet, a user would specify two inputs:
    1. Public holidays in the year; and
    2. Work start and work end time

    The task is to determine the turn around time excluding Friday, Saturday and public holidays.