• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculate TAT excluding weekends and public holidays

M

Maria Katsifis

New Member
Greetings

I need a formula to calculate the TAT time for the following start and date time and am having some difficulty with the formula that I am using.

I have a list of interactions with a START DATE/TIME and END DATE/TIME. The start time can be 24 hours however the end time will be between 09:00 and 17:00, but I need the working hours to be 7 (we exclude a lunch break). I also have a list of public holidays.
The formula that I am using BELOW WHERE:

A2 = Start Date and Time
B2 = End Date Time
C$2: C$23 = public holidays in format eg. 24/12/2013

=(NETWORKDAYS.(A2,B2,C$2:C$23)-1)*("17:00"-"09:00")+IF(NETWORKDAYS(B2,B2,C$2:C$23),MEDIAN(MOD(B2,1),"17:00","09:00"),"17:00")-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),"17:00","09:00")

I do not know what I am doing wrong with this formula as I keep getting #VALUES returned. The submission date can be anytime of the day, however the resolution date/time can only be during Mon - Fri and 9am - 5.00pm. There maybe some instances where the resolution date/time will be before 9.00am or after 5.00pm and is the formula can be modified to suit this as well this would be even better. Can some one pls tell me what I am doing wrong and provide alternative?
Many thanks
Maria
 
Thanks for this. Here is the file that I tried to upload yesterday. I'll go through the comments that you have posted also. Thanks again.
 

Attachments

  • Formula to calculate TAT excluding weekends and public holidays.xlsx
    10.9 KB · Views: 49
Hi Maria ,

In which case , first convert all of the entries to real dates ; most of them have been entered using an apostrophe before the date as in :

'24/12/2013 3:00:00 PM

which is making the data appear as TEXT.

Narayan
 
Hi Maria ,

Go to each cell , press F2 to edit , press the HOME key to take the cursor to before the apostrophe , and press the DEL key. Press ENTER to save.

Narayan
 
Thanks for that but my whole document is 22,000 cells. I cannot do this individually. Is there a faster way??? Sorry for the bother...
 
Hi Maria ,

The problem is that the holiday 26/12/2013 has been entered with leading spaces ; edit the cell and remove all of them.

Narayan
 
Hi Narayan

I am returning to this issue as I have now discovered something else when I have exceptional data . Where the start day and end day are the same but the time is different I get a negative -1 day 0.333 etc output. Also, if the enquiry was submitted on a weekend or a public holiday I get a similar negative result. Or, if the enquiry was submitted outside business hours eg. 6.30am and resolved at 9.30am the turn around time which should show 30 mins (because our working day is 9-5) shows
-1 days 6.97 hours

I am attaching the worksheet. Are you able to take a look and see what it is that is wrong with this formula? I need to build something that will say that if something is submitted outside the normal business day then it should be counted from the next business day. Is that possible?

Thanks again
Maria
 

Attachments

  • Calculating Turn around times in excel.xlsx
    15.6 KB · Views: 49
You must log in or register to reply here.
Back
Top