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.
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!
Ashish, this works perfectly!! Thanks SO MUCH for your help!
You are welcome.
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 Ashish,
We use the MS Office 2013 version.
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,
Given a beginning and ending date, to get the number of days excluding Sundays and Public Holidays use the NETWORKDAYS.INTL() function.
Hi… I replaced ’ with ‘ & applied the formulae. Now its coming correctly. Thks a lot…. Sorry to disturb you.
You are welcome. Glad to help.
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,
Excel 2010 has a NETWORKDAYS.INTL function where you can specify Saturday as a working day and give an additional list of Public holidays.
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
You are welcome.
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.
Hi,
In the original blog post, there is a file download link. Please download that file.
Hello,
I want to jst find out the time between two dates excluding only weekends(saturday, sunday).
Please help me
Pravalika
Hi,
Please upload your file to SkyDrive and share the link of the uploaded file here.
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,
Upload your file to SkyDrive/Google Docs and share the link of the uploaded file here.
Hi Ashish,
Thanks for your reply.
The URL for the file is https://skydrive.live.com/redir?resid=D033BC83FB549939!131&authkey=!AKGuN59_4TZmYU4
The beginning dates are in Column L, the end dates are in Column H and the state/territory is in Column V. The other sheet in the workbook has the public holidays for each state and territory with the ranges marked as absolute values, and the start and end of business hours also marked as absolute values.
Hi,
You may refer to my solution in column G of this workbook. Hope this helps.
If you identify any lapses, then please mark those cells in yellow and share the correct answer for those yellow coloured cells.
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
You are welcome. I have deleted the headings.
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.
You may change the end time on the next sheet from 17:00 to 17:30.
Hope this helps.
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.
Hi,
That is nice. If it yields the correct result, then it’s great
Hi,
You can try with some examples.
If it is working fine then why does anyone need to try. If you are facing a problem, then let’s try to solve that specific problem.
Thanks for your kind words.
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?
Hi,
You can simply save the workbook as Excel workbook (from the Save as Dialog box). I just tried it in Excel 2013.
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
Hi,
Thank you for correcting my formula.
Thanks for the formulas.
You are welcome.
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:-(
Thanks a lot Ashish…:-)
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.
You are welcome. Thank you for the feedback.
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,
Thank you. Upload your file to OneDrive/Google Docs or a similar service and share the link of the file. Please also explain the question clearly. Also, show the expected result in the file which you share.
Hi Ahshish,
https://drive.google.com/file/d/0B4yz2BKtccnxTDhTSGc3aTMzemc/edit?usp=sharing
Please find the file and the requirement mentioned in file.
Thanks in advance for the help.
Hi,
Is this what you want to do?
TAT hours = Total hours – Non Business hours – Holidays – hours in column B.
Please reconfirm. Also, share the Holiday list.
Holidays consider anything for now. Yes your are abosuletly right
TAT hours = Total hours – Non Business hours – Holidays – hours in column B
( But Column B hours have Weekends and Non Business Hours so bit confusing)
Thank you for the help.
Raghav
Hi,
We will not be able to remove the Weekend hours and Non business hours from the hours appearing in column B because we do not know the start and end time for computing the those hours (appearing in column B).
Hi,
Thats Ok!! Thank you for the help.
Really great to see you helping others:)
You are welcome.
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,
That link says “You need permission”
https://drive.google.com/file/d/0B4yz2BKtccnxV2VEaTlLeU5UYzQ/edit?usp=sharing
Hi ,
Please find above link.
Thank You,Raghav
Hi,
I have not checked your formula thoroughly by the reason you are getting # in some cells of column N is that the End date/time < Start date/time. Check cells A5,A4 and A7,A8. Also, please be very specific with your question. If you have already written the formula and are facing a problem in a few cells, then highlight only those cells where you need my help. Lastly, before uploading the file, please reduce the file size by only showing the problematic cells.
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,
You may refer to my solution in this workbook. If you identify any lapses, then please highlight those cells in yellow and share your expected result.
Hope this helps.
Thank You so much Ashish.
You are welcome.
Hi Ashish,
The solution is really working good.
But i have bit problem as some of the region have following shift.
Start Time: 11:30 PM
End Time : 8:AM
So in these case i am getting the TAT Hours as 0. Could you please help me with that.
Hi,
To keep things simple, I have broken down the formula into smaller parts with a different structure. Check these result thoroughly and identify lapses. Here is the link to download the workbook.
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.
Thank you, that’s great.
You are welcome.
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,
Share data and more importantly your expected solution – take a few cases to illustrate.
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.
Thank you very much for your help.
You are welcome.
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)
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,
In cell C1, the formula should be
=IF(MOD(B1,1)
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,
Refer to my solution in this workbook.
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,
If normal business hours are 8:00 am to 5:00 pm, then any request which comes after 5:00 pm should be counted as the next working day request. If this statement is true, then what is the purpose of the 7 pm sentence in your question.
Hi Ashish,
Normal business hours are 10 am to 7 pm.
Thanks again a lot for your help.
Hi,
So anytime after 7 PM will be considered on the next business day. Am I correct?
Yes.
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,
You may add the mail date and time to get the date and time in one cell. Do the same for the Creation date and time as well.
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
Hi,
Yes that is correct. 62 hours worked from Friday to Friday and 8:30 worked on May 7, 2015. Please format range C2:L3 as [hh]:mm
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.
Hi,
Please format range C2:L3 as [hh]:mm
Hi Ashish, That’s working fine. Thanks a lot.
You are welcome.
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.
Hi,
Yes. That should work. Try it.
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,
Thank you for pointing out my mistake. Here is the revised workbook.
Hello Ashish,
Thanks a lot !!, tomorrow I have the interview for which I sought your help and you have replied at the right time.
I was assuming I will be asked how to calculate TAT in interview, now I can give an answer.
Thanks a lot for your help once again !!
Regards,
Amit
You are welcome.
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.
Hi Ashish,
Seems strange… Please find link below..
Thanks in advance.
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.
Hi Ashish,
I apologize for the inconvenience. I think I was getting you wrong. Hope that this time you will have everything… Please find the link below:
BTW, I think the formula is ok since results are good for nearly all lines.
Thank you again for all your precious help.
No Problem. When I click on the link, I get a message saying that “The item might no longer exist”. You need to make to file public.
How to make it public?
Thanks
Share it exactly how you have been sharing your other files in the past. Please do a Google search for how to share files on OneDrive.
Ashish,
Hope that this one will work:
https://onedrive.live.com/edit.aspx?cid=873CA23F32B777A7&resid=873CA23F32B777A7%21120&app=Excel&authkey=%21AAGYiIvJnHj7yb4
Hi,
You may refer to my solution in this workbook.
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,
Sorry for that error on my part. Please find herein the revised workbook.
Hi Ashish,
Thanks a lot. It’s working great…
You are welcome.
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,
Upload the workbook (to OneDrive and share the link here) with the exact data layout and show the expected results for a few cases.
Hi Ashish,
Workbook uploaded: http://1drv.ms/1MOlyGu
With data layout and expected results.
Thanks again!!
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
Thanks. Can you please tell me what was the issue?
Hi,
I just added another condition to the “Same day” conditional test.
Hi Ashish,
Thanks.
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,
If the weekends are Sat and Sun, then please use the NETWORKDAYS function. If the weekend can be any other day(s), then use the NETWORKDAYS.INTL function.
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
Hi,
Thank you for writing in. I am nor clear with your expected result. Please take a few examples to show your expected result and also the rationale for the same.
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 Ashishji,
Thank you so much for replying. It so nice of you.You understand the problem correctly.
“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 & get carried forward next day shift start time”
https://drive.google.com/file/d/0B-l4SSdyBOmATzdJT1FrVmZNR1U/view?usp=sharing
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.
Thank you so much for spending your precious time for solving my problem. I am really obliged.
Problem I am observing here is
Order placed previous day before 16:00 but delivered next day after shift opening time -workbook calculating TAT till 22:00 same day whereas it should calculate TAT till 18:30 (shift end time). Explained in workbook also.
https://drive.google.com/file/d/0B-l4SSdyBOmAYU9SWDBiVlZRRXc/view?usp=sharing
Hi,
You may download the workbook from here. I have only changed the formula in column S (not in T:V).
Please check thoroughly.
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.
Dear Ashish,
Thank you for your response. Kindly see the link below:
I am getting a #### error on 2 entries maybe because it is less than 60 seconds? How do we fix this? We have this as a common case as some mails can just be rejected immediately.
Thank you for your usual support.
Oh this I already understood already because the Mail addressed date is earlier than 10:00:00 AM. 🙂
However, can you see this attached:
https://onedrive.live.com/redir?resid=D6545CD9D2142129!460&authkey=!AI2sdfJc1aaiLMA&ithint=file%2cxlsx
Receive date is not properly computing. Appreciate your assistance.
Hi,
It is working absolutely fine. You are seeing 00:00:00 in column H because May 5, 2016 is a holiday – see worksheet named PO.
Hope this helps.
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 .. We has ben using date for TAT But how we can exclusive for Holidays .. plz send formula..
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,
Hi,
I cannot create a workbook for you. However, you may download my solution workbook from the Blog article and key in your inputs there.
Hi Ashish,
Hope you can open this workbook.
https://drive.google.com/open?id=0B1YTxKKgbfaDUFE3cG1HY1pjNlJWNC1XaDNra092VnVnbjBv
Thank you in advance.
Hi,
Have you tried my solution posted in the Blog article? If it does not give you your expected result, then let me know exactly where my formula’s answers differs from yours.
Hi Ashish,
Ok so i updated Cell D2 and D3 with the time we are trying to calculate in hours.
Please note we work 24/7 except during the weekend so no holiday or working hours are involved.
https://drive.google.com/open?id=0B1YTxKKgbfaDdUlUMnRhd2tyT2RkMVhqa2E3cW5HNzFhWE0w
It calculates the time being 10:55 but it should be 21:55.
Also when i add another day before it should be 45:55 but it stays on 10:55.
Hi,
In cell C2, enter this formula and copy down
=IF(WEEKDAY(A2,2)>5,0,TIME(0,0,0)-MOD(A2,1)+(MOD(A2,1)>TIME(0,0,0)))+NETWORKDAYS(INT(A2)+1,INT(B2)+1)+IF(WEEKDAY(B2,2)>5,0,MOD(B2,1)-TIME(0,0,0))
Format the results in column C as [hh]:mm
Hope this helps.
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,
I have calculate the time, but has some conditions. Can I share the file?
Hi, file link:
https://drive.google.com/file/d/1K1CUTQGqeO_mhBy0yWdLaaX-copswR19/view?usp=sharing
Hope you can use it.
Please help me.
Hi,
Even if I can solve it, this will be a very complex one to solve. You may want to seek someone else’s help.
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
You are most welcome. Thank you for your kind words.
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.
You are welcome. Please consider replacing networkdays functions with networkdays.intl in my formula.
Hope this helps.
Thank you Ashish.
You are welcome.
Hi Ashish Sir,
I Need Your Help For Related TAT Formula
Sir, I am not able to fix this formula please help me in correcting this formula.
Sheet Link:-
https://docs.google.com/spreadsheets/d/1qY9pOrYMsecj3keUg6ASAc1dcDH7aXnblrE0T7I47ic/edit?usp=sharing
Hi,
Have you tried my solution which i have shared in the original post? If not, then please do. If you get an incorrect result, please post back.
Sir, I have tried many times, but the time is coming right, the date is getting wrong, and do not include Sunday, please help me in this.
https://docs.google.com/spreadsheets/d/1qY9pOrYMsecj3keUg6ASAc1dcDH7aXnblrE0T7I47ic/edit?usp=sharing
Sir Please give me a solution
Calculate turn around time excluding Sundays and public holidays
{ 206 Comments }