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, Attachment 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...._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.

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

Artik

[Aussiebear]

@artik ....simply too funny

[Paul_Hossler]

Not knowing beforehand of the existence of this thread, I prepared my solution on excelforum.com

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

Capture.JPG


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

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.