How To Calculate SLA or TAT by excluding non business hours || Excel Tricks

[@hariommishra8075]

Bhai i want to salute you know you give me a relief..from this pain

[@dptutorials]

Most welcome

[@nazerbor3i]

you deserve a 10000000 likes for this video, you earned the subscribe and like

[@dptutorials]

Thanks a lot. This is inspiring

[@MuhammadAmin-po9df]

Thanks alot, it is very helpful. I had very difficulty in calculating the TAT. Now got understand. Thanks.

[@shri_420]

Thank you so much .. I am sure many like me were searching for this for a long time. Also there are other pages and videos about this but you explained in a better way.

[@ammujoseph8783]

I finally understand what Median and MOD used for And lost days looking for this kind of a solution. Hats off to U👍

[@dptutorials]

Thanks a lot ammu...

[@silvestrecamposano6317]

Thank you very much Sir!... I have been looking for this formula...

[@dptutorials]

You are most welcome

[@harishkharishkrishna2762]

Thank you so much sir.. it's really helpful to whoever is looking for tat calculations as accurate as this...... U r a great teacher ...

[@dptutorials]

Most welcome

[@manuelc6315]

Thanks a lot, i was looking for this exact formulas.

[@midhunramachandran5131]

Awesome n thanks bro... ❤❤❤

[@mukeshvishal9113]

Thanks a lot. I was looking for this type of formula. Very neatly explained

[@SushilKumarRajbhar-SKR]

Please share as soon as possible

[@anupamchoudhary6168]

This is very informative and nicely explained. Thank you!

[@dptutorials]

Glad it was helpful!

[@DeeInTheMoshPit]

thank you VERY MUCH! I was looking for this for days and couldn't find a tutorial good as yours! it's exactly what I need!

[@dptutorials]

Glad I could help!

[@vishaljaiswal4721]

I need to give this one a try.

[@coolblue1524]

Brilliant!!

[@TheMaddy6666]

very helpful thank you so much

[@jayantadas8259]

Excellent

[@uLeabric]

Thank you! This solves my puzzle perfectly.

[@dptutorials]

Glad it helped!

[@joemillovich]

This works perfect for business days, but what if i only want to exclude non-business hours (i.e. 7 days a week excluding 5pm-8am)?

[@ranjitjojo]

Sir, I have a question- if it's a 24/7 queue, how to tweek the formula

[@Sharks-m8c]

This has been the formula I have been looking for for AGES, so thank you! I do have another problem... if someone works something on a Sunday (the only day we are closed) how can this still be counted? So my example is the item arrived at 5pm on Saturday, (our working hours are 8am to 6pm mon to sat) but it was then actioned on the Sunday, but I want to still count this as taking 1 hour (5pm sat to 6pm sat) can this be included? And how can I add in bank holidays to count as non working days? THANK YOU

[@SushilKumarRajbhar-SKR]

Please share as soon as possible

[@sainathsarath]

@dptutorials Thanks for the content, can you please help me with the same to add holidays list in the formula. Thanks in advance!

[@saketmaurya]

thanku man.

[@dptutorials]

Welcome

[@vivek01v]

Sir If I want to exclude Friday, Saturday, Sunday and my working hours are 8:30 to 17:30. What is the formula

[@dptutorials]

It is possible with minor modification of formula.

[@manishahegadi7550]

Did you find your values?

[@haidertalib9206]

How can the cumulative time difference between (4:00 pm - 9:00 am) be collected at the end of a month? especially if you have 100 employees

[@sailajapodganti]

Thank you!

[@subramaniansrinivasan5510]

Hi I have real time scenario Reporting date is 30-Dec-2020 17:53 and Response date is 12-Feb-2021 20:27 Both SLA/TAT & Corrected formula shows 2:34, just calculating difference of time stamp, not considering date part. Please advise how to calculate SLA considering the dates also. How to exclude holiday list in SLA. Thanks in advance.

[@sushantpratapsingh1819]

Facing the exact issue. It's not considering days. Seems like something to do with the format, but unable to figure it out.

[@ral-zoneservice5023]

Issue solved-Goo To Change Your Formats -Custom number Formats-[h]:mm

[@gurdipbansal5493]

Hey, Thank you, Really good video, I'm struggling the formula doesn't seem to work if the two days are over a month long E.g 22/01/22 08:00 - 03/02/22 10:00

[@mike4me12]

Nice

[@dptutorials]

Most welcome

[@hemantsawant3823]

Hey thanks for the solution but time is wrongly calculated when the days difference is more than 2 days. It’s not calculating more than 24 hrs.

[@jeoverzosa9962]

Hi! I tested this one considering that non business hours are from 5:00 am to 8:00 pm and it showed negative values? Would you happen to have a solution for this? The work hours are from 8:00pm - 5:00am (night shift). Thanks in advance!

[@Gmji001]

in your case u should enter ="MEDIAN(A4,"05:00","20:00") in Formula you have to enter Business hours in reverse order =MEDIAN(cell,"ClosingTime","OpeningTime")

[@whatsupwithdjpodcast]

How do I calculate running days of an outage? This seems to only work for outages that have occurred within 24 hours.

[@shezowicked2315]

I'm getting a negative value on reporting dates on a weekend outside of work hours. Any solution for this?

[@ahmadfm836]

How can I get the excel sheet file?

[@alokanand496]

Dear sir it is very helpful. I need your help. I have two cut off time, like one in day shift and one in night. How I will apply 2 cut off time in one formula

[@dptutorials]

Hi, mail me the excel at [e-mail]

[@swamyh4478]

Am working Sunday's also but this formula not accepting on Sundays duration kindly suggest

[@camlifeedition7470]

Thank for sharing but may i know why the h counting isn't work correctly when the start date and end date are different month?

[@dptutorials]

You're very welcome

[@ashwingupta7002]

How i can add priority and Holidaylist in the same formula for eg. P1 - 24 x 7 x 365 P2- 24 x 7 x 365 P3- M-F - 9:00-18:00 P4 - M-F - 9:00-18:00

[@Gautam.Khanna.excel.guruji]

Hi its a great option however if I change date for more than one day still this formula is showing the same time. How can I capture that. For example if a man response after 4 Days

[@sabduljabbar5026]

Good afternoon Sir, How to get end date and time if i have start day and time together and to add "n" working hours excluding non working hours. example 1) if start date=02/06/2022 02:00 and to add 4:00 working hours and the working hours are from 08:00 to 18:00 and the non working hours to be excluded are of from 18:00 to 08:00 and the end date should be like 02/06/2022 12:00. what is the formula for this one sir. 2) if start date=02/06/2022 16:00 and to add 4:00 working hours and the working hours are from 08:00 to 18:00 the and the non working hours to be excluded are of from 18:00 to 08:00 and the end date should be like 03/06/2022 10:00. what is the formula for this one sir. In CCC to be completed time also same formula given sir. Can u please help me with the formula sir?

[@dptutorials]

Could you please write to [e-mail]

[@razanaeem7491]

Dear, why median is used ? What is the function there ?

[@excelmaster6329]

Thanks a lot for the formula but it's not working if start date or end date comes on weekend, is there any solution for this 🙏

[@dptutorials]

Let me check

[@razanaeem7491]

Thankyou bro

[@dptutorials]

Most welcome

[@amitapattanaik1071]

Very good

[@dptutorials]

Thanks

[@sravankadaveru]

This is very good information, and useful. But, can you also help with some more info for how to consider public holidays to it. Please.

[@sizwemchunu6110]

Thank you for the tutorial. Please help, I have been struggling to edit the formula to Network days. Weekends and Holidays do not apply therefore no holidays or weekends to be omitted.

[@dptutorials]

Glad it helped!

[@subramaniansrinivasan5510]

Hi McCune Use the following to include all workdays and no weekends NETWORKDAYS.INTL(start,end,"0000000")

[@sizwemchunu6110]

@@subramaniansrinivasan5510 Thank you I will Try it out.

[@ricardomajor4189]

How can I adapt it if the ticket’s start date is on a non business day?

[@dptutorials]

It will still count the same, as SLA should not consider the non business day.

[@roberto9869]

I have a problem, i can not add working hours to formula. Any ideas why?

[@theruler9999]

Hi, thank for the valuable info, In the given example, you mentioned that it's for the case if I have two dates with two time stamps, what should be changed if I have only one date and one time stamp?

[@dptutorials]

You should make it uniform

[@hongyongng7799]

How do I adjust the formula if my working hours is from 9am to 3.30am the next day?

[@manoharramanji7025]

Formula is good but not working if complete days time is lesser than received time......if u can give me some formula for the same

[@ashtonquincey]

Great tutorial! my next question is how can i have this formula work for different times zones? for example, If the region is North America, remove any time not in x-x business hours. if the region is Asia, remove any time not in x-x hours. Do you have a tutorial for that?

[@dptutorials]

Yes, exactly, if you look at the formula, you can see the timings, which is irrelevant to the time zones in fact.

[@TheBabuthebabu]

if next day is sunday, and i am considering sunday as weekoff, results wrong.

[@dptutorials]

Oh, will correct it.

[@kirtirajpoojary1579]

This formula doest work if the end time is smaller than start time and end day falls on weekend. Ex for start date/time 18-dec-2020 1:46 pm to 19-dec-2020 12:22 pm this formula shows error.

[@dptutorials]

This should work, However, Let me check once again.

[@ramveerramveer1413]

yes formula is not working properly

[@brianwright2463]

So do you have a resolution or you're just going to leave it as is? LOL, smh.

[@MarcianFernando]

@@dptutorials Hi I also face the same issue with From time lesser than To time. Do we have a fix for this?

[@manoharramanji7025]

Same issue here

[@mukeshjangid4528]

gives the wrong output result, if the reporting date are non work day

[@ismailjohari674]

How to exclude lunch hour break in this formula, Sir?

[@ratemyshares5076]

if all days working what will be the formula like

[@dptutorials]

then simply use the networkdays function kind of.

[@ratemyshares5076]

@@dptutorials 10 hours working also

[@pripirulitos]

Thank you very much for the video, I would like to understand why the -1.

[@dptutorials]

You're welcome, -1 is to count the reporting date as well.

[@manishahegadi7550]

Count the reporting date as in?? That a great video actually but I still did not understand why -1. After I used your formula I ended up with something like this "1900-01-01 1:41"

[@DrakkenTheLastKnight]

Hi, thank you for the tutorial. Just one Q, how to manage formula to start counting SLA if the ,start time, is weekend and ,end time, is during business hours? Thanks!

[@dptutorials]

Yes, correct. This formula is for business hours. For other cases, you need to tweak this formula a bit.

[@mateogigena]

This stopeed working for me! All of a sudden... Been using this formula (with date and time on same cell) for months and it suddenly starting rounding up some numbers, it is not more accurate.

[@dptutorials]

Please send me your excel file. Will fix it.

[@mateogigena]

@@dptutorials Thanks for the response! It seems like it fixed by itself... seems like a google issue (working on google sheets)

[@shashibhushan535]

I have tried this but result is not showing correct

[@Riya4s]

I appreciated, which is I'm an expected the same formula I got it. Let me workout if anything let you know.

[@Him_Purush]

This formula doesn't work if 1. Reporting date or Response date falls on Weekend 2. Reporting date and Response date both are not the working dates. for e.g. reporting and response is 6 PM and 8 PM resp. 3. Reporting Date and Time is after working time

[@sanjeshtiwari123]

I am unable to use this formula in my excel

[@dptutorials]

Please share your excel at [e-mail] for solution

[@zclowngaming787]

It shows wrong value when I enter two dates that have interval of more than 2 days. Try entering Reporting date and time as 4-04-22 10 AM and response date and time as 6-04-22 10 AM, it shows 0 Hours. If I enter anything more than 2 days interval, it shows wrong value.

[@MohitChauhan-ju8iq]

This formula is not working if date difference is beyond 7 days

[@davidferrick]

YOu still excluded a weekend (Sunday) in your formula.

[@Priya_Just4Fun]

Formula doesn't work on date it just calculate time if both date or same

[@swaraj2653]

It's not working for 2022 year

[@dptutorials]

Hi, I have checked and found that it is working fine. Please retry once more

[@padansri143]

Bro why your doing -1

[@dptutorials]

To exclude counting the start date itself

[@yogeshlakra007]

why -1 used

[@dptutorials]

to convert into time format

[@themarket1992]

What if someone has worked after 9pm.

[@rosalierom3217]

May I know why did you put minus 1 in the formula in networkdays.intl?

[@dptutorials]

To exclude the current date.

[@sahi04]

It's not calculate the correct time

[@DVG3591]

I don't think it's right calculation