Need help with TAT calculation

[sam_011]

Hey bud's..
I am in need of help, so my company asked me to calculate our TAT (turn around time) for our ticket's, but there is some consideration's i need to look for.
1) our office work 24 hour's with some TAT off time window.
2) our TAT stop on Friday 5:30 pm and start again on Sunday 10:30 PM.
3) any ticket which come after 17:30 on Friday will start TAT on 22:30 Sunday.
4) For all day's our TAT stop from 6:30 AM to 7:30 AM and 16:30 to 21:30 for which all TAT is not counted between these window also any request which arrive between off time will start TAT ON time window.

I managed to calculate TAT time for point 1 2 and 3 by access query's but i need help for calculating for point 4 for which a excel macro will be helpful.


I am struggling with point 4 as i have to stop TAT for all request two times daily and on Friday our TAT effetely on 16:30 and start on 22:30 Sunday.


I am attaching a screenshot of what i calculated with access, 781250
In snapshot column A has unique ticket number, column D has ticket start time, column B has ticket end time, column C has TAT time as per point 1 2 3.


I need help to create macro for calculating TAT for point 4, i already has point 1 2 3 covered and calculated in column C.


also there i calculated TAT minutes in Column C till 17:30 Friday but our time stops at 16:30 on Friday.


any help will be grateful.


Thanks buds

[Paul_Hossler]

1. Attaching a sampe workbook is much better than a picture

2. I don't see anything in the picture that mentions points 1,2,3 or 4 so I think a LOT more explainatin is needed, and maybe a manually calculated example

3. Top of my head, it might be better to have all the points in Excel instead of what sounds like Access

[Aussiebear]

cross posted at Excelforum.com. What a shame.

[Paul_Hossler]

Yea

@sam_011 -- Please take a minute to read the FAQs for the forum, especially the part about multi-posting

http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#faq_new_faq_item3


What is multiposting?Multiposting is the act of posting a single question to different forums around the same time.

We discourage multiposting because it is like calling five cab companies and going with the one that comes first -- it shows disrespect for the volunteers that monitor this forum because they will unknowingly be helping someone who may have already received help elsewhere. Their time is no less important than that of the person asking the question.

Many of the volunteers here visit multiple forums and can easily spot duplicate posts. Some of them may even reply to your posts with a link to your post on another forum. Don't be the person that gets caught.

If you must post your question on a different forum, include a link to the question you have already posted on the previous forum(s). That way, those helping you can decide for themselves if you are already receiving the help you need somewhere else.



If you are still confused, read A message to forum cross posters (http://www.excelguru.ca/node/7).


The link there in the FAQ

https://www.excelguru.ca/content.php?184

explains (very politely) the correct way to ask a question in more than one forum.

We really do want to know so that if you got an answer from another forum, we don't spend unnecessary time reinventing the wheel (or spreadsheet)

[sam_011]

1. Attaching a sampe workbook is much better than a picture

2. I don't see anything in the picture that mentions points 1,2,3 or 4 so I think a LOT more explainatin is needed, and maybe a manually calculated example

3. Top of my head, it might be better to have all the points in Excel instead of what sounds like Access

Hello,
I attached workbook sample, in that i already calculated TAT in column C, i calculated for TAT between Sunday 22:30 to Friday 17:30 without and mentioned off window in point 4, need help for point 4. column 4 has start time and column B has end time of ticket.

If we can do this via access then it's also fine.

[sam_011]

Sorry, i am new here don't know all rules, also i am in kind of urgency my manager want it by Monday morning.
will you help a brother out.

i posted this issue also on excelforum.com/excel-programming-vba-macros/1378570-need-help-with-tat-calculation.html

[Aussiebear]

Same rule over at Excelforum too, as is the case with almost all other vba forums.

[Paul_Hossler]

OK, here's my first attempt at it

I used a User Defined Function called TAT()

Look at the attachment worksheet 'test' and see




Option Explicit


'1) our office work 24 hour's with some TAT off time window.
'2) our TAT stop on Friday 5:30 pm and start again on Sunday 10:30 PM.
'3) any ticket which come after 17:30 on Friday will start TAT on 22:30 Sunday.
'4) For all day's our TAT stop from 6:30 AM to 7:30 AM and 16:30 to 21:30 for which all
' TAT is not counted between these window
'also any request which arrive between off time will start TAT ON time window.


Const t0000 As Long = 0
Const t0630 As Long = 390
Const t0730 As Long = 450


Const t1630 As Long = 990
Const t1730 As Long = 1050


Const t2130 As Long = 1290
Const t2230 As Long = 1350
Const t2400 As Long = 1440


Function TAT(S As Date, E As Date) As Double


Dim dateStart As Date, timeStart As Date, dateEnd As Date, timeEnd As Date ' start/end dates times
Dim dowStart As VbDayOfWeek, dowEnd As VbDayOfWeek
Dim i As Long, cnt As Long
Dim cumTAT As Double

dateStart = Int(S)
timeStart = S - dateStart
dowStart = Weekday(dateStart)

dateEnd = Int(E)
timeEnd = E - dateEnd
dowEnd = Weekday(dateEnd)

'3) any ticket which come after 17:30 on Friday will start TAT on 22:30 Sunday.
If (dowStart = vbFriday) And (timeStart >= TimeSerial(17, 30, 0)) Then
timeStart = TimeSerial(22, 30, 0)
dateStart = DateSerial(Year(dateStart), Month(dateStart), Day(dateStart) + 2) + timeStart ' add 2 to get Sunday
dowStart = Weekday(dateStart)

ElseIf (dowStart = vbSaturday) Then
timeStart = TimeSerial(22, 30, 0)
dateStart = DateSerial(Year(dateStart), Month(dateStart), Day(dateStart) + 1) + timeStart ' add 1 to get Sunday
dowStart = Weekday(dateStart)

ElseIf (dowStart = vbSunday) And (TimeSerial(22, 30, 0)) Then
timeStart = TimeSerial(22, 30, 0)
dateStart = DateSerial(Year(dateStart), Month(dateStart), Day(dateStart) ) + timeStart ' already synday
dowStart = Weekday(dateStart)
End If


'same day
If dateStart = dateEnd Then
TAT = OneDay(S, E)
Exit Function
End If

cumTAT = 0#


'partial first day
cumTAT = OneDay(S, 0) ' 0 = special flag for 24:00:00 of same day

'partial last day
cumTAT = cumTAT + OneDay(0, E)


'inside days
cnt = 1
For i = dateStart + 1 To dateEnd - 1
cumTAT = cumTAT + OneDay(DateSerial(Year(S), Month(S), Day(S) + cnt), 0)
cnt = cnt + 1
Next i

TAT = cumTAT
End Function


'4) For all day's our TAT stop from 6:30 AM to 7:30 AM and 16:30 to 21:30 for which all
' TAT is not counted between these window
'
'Zone 1 = 0000 <= 0630
'Zone 2 = 0630 <= 0730
'Zone 3 = 0730 <= 1630
'Zone 4 = 1630 <= 2130
'Zone 5 = 2130 <= 0000


Function OneDay(S As Date, E As Date) As Double
Dim minStart As Long, minEnd As Long
Dim cumOneDay As Double

If S = 0 Then
minStart = 0
Else
minStart = Round(24 * 60 * (S - Int(S)), 0)
End If

If E = 0 Then
minEnd = t2400
Else
minEnd = Round(24 * 60 * (E - Int(E)), 0)
End If


'if S in Zone 2 or Zone 4 then move to end of zone
'S in Zone 2
If t0630 < minStart And minStart <= t0730 Then
minStart = t0730
'S in Zone 4
ElseIf t1630 < minStart And minStart <= t2130 Then
minStart = t2130
End If

'if E in Zone 2 or Zone 4 then move to Start of zone
'E in Zone 2
If t0630 < minEnd And minEnd <= t0730 Then
minEnd = t0630
'E in Zone 4
ElseIf t1630 < minEnd And minEnd <= t2130 Then
minEnd = t1630
End If



'S in Zone 1
If minStart <= t0630 Then

'Zone 1
If minEnd <= t0630 Then
cumOneDay = minEnd - minStart
'Zone 3
ElseIf t0730 <= minEnd And minEnd <= t1630 Then
cumOneDay = minEnd - minStart - 60
'Zone 5
ElseIf t2130 <= minEnd And minEnd <= t2400 Then
cumOneDay = minEnd - minStart - 60 - 300
End If

'S in Zone 3
ElseIf t0730 <= minStart And minStart <= t1630 Then

'Zone 3
If t0730 <= minEnd And minEnd <= t1630 Then
cumOneDay = minEnd - minStart
'Zone 5
ElseIf t2130 <= minEnd And minEnd <= t2400 Then
cumOneDay = minEnd - minStart - 300
End If

'S in Zone 5
ElseIf t2130 <= minStart And minStart <= t2400 Then
cumOneDay = minEnd - minStart
End If

OneDay = cumOneDay
End Function

[Artik]

Not knowing beforehand of the existence of this thread, I prepared my solution on excelforum.com (https://www.excelforum.com/excel-programming-vba-macros/1378570-need-help-with-tat-calculation.html#post5678079)

Artik

[Aussiebear]

@artik ....simply too funny

[Paul_Hossler]

Not knowing beforehand of the existence of this thread, I prepared my solution on excelforum.com (https://www.excelforum.com/excel-programming-vba-macros/1378570-need-help-with-tat-calculation.html#post5678079)

Artik

I don't belong to that one, so could you please re-post over here so I can see?

Thanks

[Artik]

Załączony

artykuł

[Paul_Hossler]

Thanks

Yours is much faster

I had thought about doing a sub with array approach, but wasn't sure how the data actually is on a real WS, so I went with my (old favorite) UDF approach

[Aussiebear]

@Paul & Artik. I'm getting odd results when testing using the following data TAT Start 1/5/22 22:45 TAT End 12/5/22 8:45. Given that 1/5/22 is a Sunday, this problem Tat ends on a Thursday (12 days later). Using Artiks solution which calculates out at 153 hrs yet a manual calculation results in 197hrs. Why is there a difference?

[Paul_Hossler]

OK, I'm guessing here

!. Australian Daylight Savings Time?

2. Date formats as dd/mm/yyyy ???

3. Phase of the moon?

4. Solar eclipse?

5. Poor surf conditions?

6. I made a mistake and didn't handle some special cases??


I come up with 8040 minutes or 134 hours with my latest function. I think I did the manual checks correctly

29780


May 2 00:00 is the start of the day. I wasn't sure how Excel handles midnights

So 5/1 24:00 is the same as 5/2 00:00

[Aussiebear]

Must be the Aussie weather... 24 hrs = 1440 minutes. My additions ( Sun 1.5,+Mon 24,+Tue 24,+Wed 24,+Thu 24,+Fri 17.5,+Sat 0,+Sun 1.5,+Mon 24,+Tue 24,+Wed 24,+Thu 8.75)=197.25


Mutter.... damn weather.....:jail:

Oops... misread Post 1. I was thinking Sam_011 worked 24 hrs Monday to Thursday. My apologies to you & Artik

[Paul_Hossler]

My first version of the macro can up with the wrong total of 11,340 hours or 189 hours so I'm glad you caught it

I think, based on what I hope is an accurate manual calculation, that the ver 2 macro is more accurate, BUT the OP's business rules are still a little uncertain to me

[Aussiebear]

Well Sam-011's claim in his first post " that they work 24 hours with some time off.." was clearly misleading. I should have re read that statement.

[Paul_Hossler]

Well Sam-011's claim in his first post " that they work 24 hours with some time off.." was clearly misleading.

It was contradicted / redefined / interpreted by the rest of the post

This is why I always try to get the business rules clearly defined.


An originator may know exactly the way things work, but forcing them to clearly spell the rules out so that someone else understands is usually very valuable, even if the originator thinks it's a waste of time because "It's obvious"

[georgiboy]

An originator may know exactly the way things work, but forcing them to clearly spell the rules out so that someone else understands is usually very valuable, even if the originator thinks it's a waste of time because "It's obvious"

So true it's painful sometimes, the amount of times I ask someone at work to put something in layman's terms and just give me the constraints.

[Aussiebear]

Talking about the OP.... Haven't heard from him in 4 days. Maybe his boss has shot him?

[Paul_Hossler]

Dunno, but it was a fun little project in any event

After someone retires, it's important to keep the old brain cells working