• 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.

Help with TAT Calculation

B

bhavinmashruwala

New Member
I have a following Condition. I want to calculate the TAT in Hours:Minutes:Seconds. Pls help


Start Date

11/26/11 12:26 PM


End Date

12/7/11 7:06 PM


Sunday is a non- working day

working time is 7am to 10pm.
 
Hi Narayan,


Thanks for your interest in helping me.... :)


I want to calculat The TAT in HH:MM:SS (Hours:Minites:Seconds) between start date and End date.

Means from the time i have started, in how many hours i have ended the activity.. with the follwing exception to be excluded from the calculation.


Exception

1. Sunday is a non- working day (all 24 hours to be excluded from the TAT)

2. working time is 7am to 10pm. (12:00 AM to 07:00 AM & 22:00 to 24:00 Hrs to be excluded)
 
Hi ,


I am not sure whether one formula can give you the final result.


One way to get the result is to have the start date , and then the dates after that one after the other , till you come to the end date.


Thus , suppose you have 11/26/11 12:26 PM in cell A1 ; you have 11/27/2011 in A2 , 11/28/2011 in A3 and so on till you have 12/7/11 7:06 PM in A12.


In cell B1 , you have the end time for that date viz. 11/26/2011 10:00 PM ; in cell B12 , you have the start time for that date viz. 12/7/2011 07:00 AM.


In cell C1 , you have the =B1-A1 ; in cell C12 , you have =A12-B12.


In cell D1 , you have =IF(WEEKDAY(A1)=1,0,if(isblank(C1),13,C1)) ; copy this down to the other cells D2 through D12. Summing D1 through D12 gives you 104.902777777774 hours.


Breaking this down to hours , minutes and seconds = 104 hours , 54 minutes and 10 seconds.


Narayan
 
Thanks for your help..

I think this formula is difficult to feet in my sheet as i have the lot of Start & end date in column raning in thousands.. like following for exaple.

Column A Colume B

Start Date End Date

12/1/11 3:00 AM 12/2/11 11:00 AM

12/1/11 9:16 AM 12/3/11 1:45 PM

12/1/11 8:01 AM 12/3/11 12:49 PM

12/1/11 8:01 AM 12/3/11 11:11 AM

12/1/11 5:40 AM 12/3/11 12:00 AM

12/1/11 3:28 AM 12/3/11 2:18 PM

12/1/11 3:24 AM 12/3/11 10:20 PM

12/1/11 1:14 AM 12/3/11 11:00 AM

12/1/11 12:01 AM 12/3/11 11:17 AM

12/1/11 10:05 AM 12/3/11 12:00 AM

12/1/11 10:05 AM 12/3/11 12:00 AM

12/1/11 10:02 AM 12/3/11 12:00 AM


I want my answer in Colum C. We can use few working colum as well. Ihave applied the follwing cormula in Colume C & Colum D.. But it is not giving proper output.Pls help / Rectifying the formula / Logic


In Colum D to apply those exception of Hours & Sunday off

=IF(TEXT(A2,"HH:MM:SS")<"07:00:00","07:00:00"-TEXT(A2,"HH:MM:SS"),0)+IF(AND(TEXT(A2,"HH:MM:SS")<"07:00:00",DAY(B2)>DAY(A2)),"07:00:00"-TEXT(A2,"HH:MM:SS")+"02:00:00",0)+IF(AND(TEXT(A2,"HH:MM:SS")>="07:00:00",TEXT(A2,"HH:MM:SS")<"22:00:00"),0,0)+IF(AND(TEXT(A2,"HH:MM:SS")>="07:00:00",TEXT(A2,"HH:MM:SS")<"22:00:00",DAY(B2)>DAY(A2)),"02:00:00",0)+IF(AND(TEXT(A2,"HH:MM:SS")>="22:00:00",TEXT(A2,"HH:MM:SS")<"24:00:00"),"24:00:00"-TEXT(A2,"HH:MM:SS"),0)
 
Hi, bhavinmashruwala!

Try this formula:

=SI(DIASEM(A2;2)=7;0;SI(O(A2-ENTERO(A2)<HORANUMERO("07:00");A2-ENTERO(A2)>HORANUMERO("22:00"));0;HORANUMERO("22:00")-(A2-ENTERO(A2))))+(ENTERO(B2)-1-ENTERO(A2)+1-1-SI(SI(7-DIASEM(A2+1;2)<=ENTERO(B2)-1-ENTERO(A2)+1-1;7-DIASEM(A2+1;2);0)>0;ENTERO((ENTERO(B2)-1-ENTERO(A2)+1-1-SI(7-DIASEM(A2+1;2)<=ENTERO(B2)-1-ENTERO(A2)+1-1;7-DIASEM(A2+1;2);0))/7)+SI(RESIDUO(ENTERO(B2)-1-ENTERO(A2)+1-1-SI(7-DIASEM(A2+1;2)<=ENTERO(B2)-1-ENTERO(A2)+1-1;7-DIASEM(A2+1;2);0);7)=0;0;1);0))*(HORANUMERO("22:00")-HORANUMERO("07:00"))+SI(DIASEM(B2;2)=7;0;SI(O(B2-ENTERO(B2)<HORANUMERO("07:00");B2-ENTERO(B2)>HORANUMERO("22:00"));0;(B2-ENTERO(B2))-HORANUMERO("07:00")))

-----> in english:

=IF(WEEKDAY(A2,2)=7,0,IF(O(A2-INTEGER(A2)<TIMEVALUE("07:00"),A2-INTEGER(A2)>TIMEVALUE("22:00")),0,TIMEVALUE("22:00")-(A2-INTEGER(A2))))+(INTEGER(B2)-1-INTEGER(A2)+1-1-IF(IF(7-WEEKDAY(A2+1,2)<=INTEGER(B2)-1-INTEGER(A2)+1-1,7-WEEKDAY(A2+1,2),0)>0,INTEGER((INTEGER(B2)-1-INTEGER(A2)+1-1-IF(7-WEEKDAY(A2+1,2)<=INTEGER(B2)-1-INTEGER(A2)+1-1,7-WEEKDAY(A2+1,2),0))/7)+IF(MOD(INTEGER(B2)-1-INTEGER(A2)+1-1-IF(7-WEEKDAY(A2+1,2)<=INTEGER(B2)-1-INTEGER(A2)+1-1,7-WEEKDAY(A2+1,2),0),7)=0,0,1),0))*(TIMEVALUE("22:00")-TIMEVALUE("07:00"))+IF(WEEKDAY(B2,2)=7,0,IF(O(B2-INTEGER(B2)<TIMEVALUE("07:00"),B2-INTEGER(B2)>TIMEVALUE("22:00")),0,(B2-INTEGER(B2))-TIMEVALUE("07:00")))

If I made any mistake in the translation, download file from the below link, and you'll get the english translation and the option between using no auxiliary columns (the ut-supra formula) and using them and getting a little more readable spreadsheet.

http://www.2shared.com/file/_xJLq1Xt/Help_with_TAT_Calculation__for.html

Regards!
 
Hi Bhavin ,


I am not very comfortable with having formulae for the sake of formulae ; using helper columns is easier to understand and test.


Check out the worksheet at the following link , and let me know if there are any mistakes.


https://skydrive.live.com/#!/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21123


Narayan


P.S. Do not click on the hyperlink ; copy the entire address and paste it in your browser.
 
Hi, bhavinmashruwala!

I was looking to the file that uploaded NARAYANK991 and I think it doesn't reflect the real TAT.

Try from 01/12/2011 09:00 to 23/01/2012 21:35. That formula shows 567h 34m 1s and the real value is 687h 35m 0s. Despite the rounding error I believe the calculations are not exact by more than 20%.

Regards!
 
Hey friends, Sorry for delay in revert as i was not in town for some personal work.....

And thanks for your help..

But I think I have still not able to convey my requirement properly.. I wil explain it once again with exact example.


Example 1.

Start Time 12/1/11 07:00 AM and End Time 12/3/11 9:00 AM.

Here TAT should be 17 Hrs as Start Date or End Date is not a Sunday. My working hours Starts from 7:00 AM and Ends & 10:00 PM so 15 Hrs from 1st Dec & 2 Hrs from 2nd Dec. Total TAT should be 17 Hrs. Means i took 17 Hrs to finish the activity.


Example 2

Start Date 12/4/11 9:00 AM & End Date 12/5/11 9:00 AM

Here TAT should be 02:00 Hrs as Start date is a Sunday and my so 0 Hrs from 4th Dec & End date is Monday. My Working hours start from 7 AM and i have finished the activity @ 9:00 AM so 2 Hrs from 5th Dec.


Example 3

Start Date 12/1/11 11:00 PM & End Date 12/2/11 10:00 AM

Here TAT Should be 3:00 Hrs as both are working day but i got the activity post 10:00 PM on start date which is out of my working hours so 0 Hrs from 1st Dec & have finished my activity on 2nd Dec @ 10:00 AM so 3 Hrs from 2nd Dec.


Also there may be possibility where there is a huge differance between start date and End Date and few sundays in between.

Example 4

Start Date 12/1/11 7:00 AM & End Date 12/20/11 9:00 AM

Here TAT Should be 242 Hrs as shown below.


Dates, Hrs, Day

12/1/11 7:00 AM, 15 Hrs, Thu

12/02/11, 15 Hrs, Fri

12/03/11, 15 Hrs, Sat

12/04/11, 0 Hrs, Sun

12/05/11, 15 Hrs, Mon

12/06/11, 15 Hrs, Tue

12/07/11, 15 Hrs, Wed

12/08/11, 15 Hrs, Thu

12/09/11, 15 Hrs, Fri

12/10/11, 15 Hrs, Sat

12/11/11, 0 Hrs, Sun

12/12/11, 15 Hrs, Mon

12/13/11, 15 Hrs, Tue

12/14/11, 15 Hrs, Wed

12/15/11, 15 Hrs, Thu

12/16/11, 15 Hrs, Fri

12/17/11, 15 Hrs, Sat

12/18/11, 0 Hrs, Sun

12/19/11, 15 Hrs, Mon

12/20/11 9:00 AM, 2 Hrs, Tue

Total Hrs, 242 Hrs.
 
Hi Bhavin ,


Can you check out the modified worksheet at the following link ?


https://skydrive.live.com/?id=754467BA13646A3F!124&cid=754467ba13646a3f&&event_source=CloseButton#!/view.aspx?cid=754467BA13646A3F&resid=754467BA13646A3F%21124


Please note that the formulae in column S are array formulae , to be entered with CTRL SHIFT ENTER.


Narayan
 
Hey Narayan...Thanks your formula its Working now..

But I also want to share my formula which i have made just now.. pls let me know how do i share the excel file with you.


Also let me know if i have made any mistake..in my file...


SirJb7: Thanks to you as well for your answers..


Its been great pleasure to talk to you guyes... & thanks to Chandoo.org as well..
 
Hi Bhavin ,


Thanks for the feedback. Please verify that everything is working the way it is supposed to ; that is the reason I have used several helper columns , so that anyone can verify all the steps that have been used to arrive at the final answer ; using one formula that contains more than 500 characters is not the way to go.


To share a file , you can open a free account on Windows skydrive at https://skydrive.live.com/ , upload your file there , and post the link here.


Narayan
 
Hi Narayan,


Pls find my file @ below link. Also let me know the feedback as well.


https://skydrive.live.com/?id=688D5DF51163B9EB!107&cid=688d5df51163b9eb&&event_source=CloseButton#!/view.aspx?cid=688D5DF51163B9EB&resid=688D5DF51163B9EB%21107


Regards,

Bhavin
 
Hi Bhavin ,


I am not able to access the file ; in addition to uploading your file , you have to set its permissions to EVERYONE (PUBLIC) ; by default , any file that you upload , is accessible by only you.


Narayan
 
Hi Narayan,


Pls try now..

https://skydrive.live.com/?cid=688D5DF51163B9EB#!/view.aspx?cid=688D5DF51163B9EB&resid=688D5DF51163B9EB%21107


Regards,

Bhavin
 
Hi, bhavinmashruwala!

I downloaded your file in skydrive and I replaced the data in row 2, col A and B, for the very same dates & times I used to develop the solution uploaded to 2shared and that I've checked manually... and I still can't get the correct result.

Try from 01/Dec/2011 09:00 to 23/Jan/2012 21:35. You should get 687h 35m 0s.

And you get:

Start Date End Date Start Date End Date Net Working Day Start Date TAT TAT Between Two Dates End Date TAT Final TAT Customer TAT

12/1/11 9:00 AM 1/28/12 9:35 PM 12/1/11 9:00 AM 1/28/12 9:35 PM 51 13:00:00 270:00:00 14:35:00 297:35:00 1404:35:00

I don't know which TAT is supposed to be the right one (if 297 or 1404), but I'm sure that none of them do the job.

About the post related to a 500-chars formula, I previously stated clearly that it was in one cell just in case you couldn't use auxiliary columns, and I recommended to use them and I led you towards my uploaded example.

Just let me know if you need further help.

Regards!

PS: maybe such a large formula is not the way to go, but at least it performs as expected.
 
Thanks SirJB7 for highlighting the error in formula.


I have rectifiled one formula.. Please check the file now..

We will reffer the Final TAT colum for the result..


Both are requested to check the file & let me know incase still there is any error in the file.


Thnks both of you for the support.


https://skydrive.live.com/#!/view.aspx?cid=688D5DF51163B9EB&resid=688D5DF51163B9EB%21108


Regards,

Bhavin
 
Hi Bhavin ,


I have gone through your worksheet , and I have two points to make :


1. Any computer software , whether it is a formula or VBA code , is an implementation of logic. For it to reflect reality , or what we think is reality , two points need to be noted - the logic should be the correct logic , and the implementation should mirror the logic exactly. If either of these is wrong , the end result is bound to be wrong.


2. Trying to verify the correctness of logic by test data , is an approach to be taken where the code is either so complex or the data so fuzzy that the logic cannot really be verified on its own. Even if the result of running a 1000 items of data through a piece of logic gives the correct , expected result , the 1001st item of data can give the wrong result. This is the basis behind all scientific proof ; one invalid result can disprove a theory. Even a thousand valid results cannot prove a theory.


What is needed for you is to verify the logic behind the calculations ; it is better if you do it yourself , since , in future , if any changes are required , you can do them on your own instead of again resorting to a forum for help.


Going by the column labelled Customer TAT , the results in that column are obtained by a simple subtraction of the start date from the end date. Thus , almost every entry in that column is different from that obtained by your calculations. You need to discuss this with the customer ; is your method right or is theirs ?


Narayan
 
Hey Narayan,


Agreed on you notes...that goes without saying.


But to be confident on my formula which i have made post your help & post looking at the logic which you have applied in your sheets weather i am not doing any error in

building the formula i have put it on the forum..


As you guys are the masters of EXCEL... & i am learning the new things.


Also the second file which you have posted on the Skydrive (using array formula) i could not understand the formula properly, becasue i don't know how to tackle Array Formula, Infact i have never used it... I have made my file considering the thought that if there is any change in logic i may not be able to edit your file whcih is made with Array formula.


Also would love to learn the array formula would request you to tell me the best way to start with.


Thanks & Regards,

Bhavin
 
Hi Bhavin ,


First , let me say that I am not a master of Excel ; ever since I joined this forum , I have learnt quite a lot. In fact , a lot of my learning has happened because in order to answer someone's question , I have had to refer to answers / hints in various websites and arrive at the answer.


The array formula which you mention was from this link :


http://www.youtube.com/watch?v=uoED6-YF7dE


The combination of INDIRECT and ROW functions creates the array of dates required to evaluate the WEEKDAY function. The only problem in this step was that the INDIRECT function was not working the way it is supposed to ; eventually I found out that was because the dates that I was using ( the start date and the end date ) were not integers , since they were not referring to midnight. This is the reason "07:00" is being subtracted from these dates. Once you have integers as the parameters for the INDIRECT function , everything works perfectly.


The problem with the NETWORKDAYS function is that it excludes the weekends i.e. both SATURDAY and SUNDAY ; in your calculation you need to exclude only SUNDAY. So to arrive at the correct number of days between two dates , you have to use the WEEKDAY function , and check to see if it 1 ( SUNDAY ). To do this in one cell , you have to create an array of dates within memory ; one way is to use the INDIRECT and ROW functions.


The best way to see how a formula works , is to use the Evaluate Formula feature within Excel ; place your cursor on the cell which contains your formula , and click on the Evaluate Formula button , and step through the evaluation process. You can see the values returned by the INDIRECT function , and the array of rows ( which are actually dates , since the start number and the end number are actually the numeric values of the dates ) being created ; using the WEEKDAY on each of these , enables you to exclude the SUNDAYS within this date range.


As far as array formulas go , one very good document is by Bob Umlas. Refer :


http://www.emailoffice.com/excel/arrays-bobumlas.html


Narayan
 
Hey Narayan,


Just to reitrate you point on NETWORKDAYS Funcition. In Excel 2010 MicroSoft has introduced one more Function called NETWORKDAYS.INTL through which you can customized your week Offs. Mean you can exclude only Sunday (or any day of the week of your choice)from the calculation.


Just FYI. I thought will be helpful to you.


Regards,

Bhavin
 
Hi, bhavinmashruwala!

Congratulations! I think you've got the TAT under absolute control. And thanks for the info on NETWORKDAYS.INTL, I didn't know it. I love learning, improving and helping a little more each day.

Anything you need, you know... somebody'll be here.

Regards!
 
You must log in or register to reply here.
Back
Top