Question Need help on TAT calculation for working hours

[Gaurav]

Hi,
I am new here and i want some help urgently, i am working on a project and i got stuck on a issue, "our company accepts new orders from sunday 10:30 pm to friday 5:30 pm, we have to complete these orders under 24 hours, if orders comes after 5:30 pm till 10:30 pm est its TAT start from 10:30 and i have to produce data for all orders in table either they in TAT or out TAT, i am using access and this weekend condition is giving me trouble, please someone point me to right direction or give code for this if some one has it already, i am in kind of hurry.
Thanks for help.

 

[Mark_]

Quicky logic...
WeekDay(YourDateHere) will tell you which day of the week a date is in, with 1 being Sunday and 7 being Saturday.

Sooo.

Select Case WeekDay(YourDateHere)
   Of 1
   ..Code here to check if its after 10:30, if so use a function you write to say if it meets your requirement or not
   Of 2 to 5
      Function you write to say if it meets your requirement or not
   of 6
... check if before 5:30pm, if so use a function you write to say it meets your requirements or not
   of 7
      Call function to state you have an order outside normal time. Should also be called in the if NOT for 1 and 6 when they check their time range.
END Select

Pretty much you have to ask, What day is it? If its a day I care about time, is it in the time?

If it is a product sold AFTER 5:30pm on Friday but BEFORE 10:30 pm on Sunday, what should happen? What do you need to update to say it is/isn't within your 24 hour window? What fields will tell you if its was processed in time or not?

I hope this give you a basic idea of what you need to do, but there are a lot of items you didn't include that should (hopefully) tell us what code you would need.

 

[Gaurav]

Hi Mark_ ,
Thanks for reply, i am trying to solve this with a some type of DateDiff formulae as access do not have networkdays.intl like excel or custom networkdays formula built in, i have tried
"Column = DATEDIFF(
if(
WEEKDAY(Table1[Received_Time];2)>=5 && Table1[Received_Time]>timevalue("6:00:00 PM")
;DATEADD(Table1[Received_Time].[Date]
;8-WEEKDAY(Table1[Received_Time];2);DAY)
+TIMEVALUE("09:00 AM")
;Table1[Received_Time])
;Table1[CompletionDate]
;HOUR)"

also tried this code" Expr1: DateDiff("d",[start],[end])-DCount("*","table1","HolidayDate BETWEEN [start] AND [end]") " with a table named Holiday date containing friday 5:30 pm in date time format and sunday 10:30pm with table1 as source data in query as mentioned from other post but no success.
Now i am on same thinking as you to use select case statement, if order come after 5:30pm friday till 10:30 pm sunday then we just start its TAT from 10:30 pm sunday no need to do any other action, this is trouble area as how can we start TAT from 10:30 pm need logic on this one.
Now i am thinking to use counter to count how many orders come in offtime as all new orderid is incremented by 1 so that when its 10:30 pm we can add those orderid on top of orderid we received before 5:30 friday and set their TAT from 10:30 sunday.
any thoughts on this.

 

[The_Doc_Man]

Watch your syntax, Mark.

Select WeekDay(YourDateHere)
   Case 1
   ..Code here to check if its after 10:30, if so use a function you write to say if it meets your requirement or not
   Case 2 to 5
      Function you write to say if it meets your requirement or not
   Case 6
... check if before 5:30pm, if so use a function you write to say it meets your requirements or not
   Case 7
      Call function to state you have an order outside normal time. Should also be called in the if NOT for 1 and 6 when they check their time range.
END Select

 

[isladogs]

Post #3 was moderated. This is to trigger email notifications

 

[Mark_]

Thanks Doc. That's what I get for writing, changing and not proofing.

Gaurav,
Have you looking into writing your own function? You would pass it the date and it would return what you need for your query. I am not clear on exactly what you are trying to return though. Please write out in as clear of a way as possible exactly what you want returned for your query.

 

[Gaurav]

Hey Mark,
Let me elaborate, Lets say i work on IT support and my work window is from 10:30 Pm sunday to 5:30 pm friday, when a ticket comes to system my team have 24 hours TurnAroundTime to solve it, however my team do not work on weekend from 5:30 pm friday till 10:30 pm sunday, so all tickets clock which arrived before 5:30 should stop on 5:30 friday and resume from 10:30 sunday also any ticket comes after 5:30 starts there TAT at 10:30 pm sunday. so i have 2 columns in table one for ticket_in_time and ticket_out_time, i want to calculate TAT for all tickets as i miss their tat or not with weekend condition.

I am trying to do it myself by making some query or formulae however i need it by today as i have a job assessment today so any help would be a blessing.
Thank You.

 

[Mark_]

If it is simply "out - in", you should already be able to do that.

Since you want to skip time during the weekend, you'd be best with a function. Pass the function the Date/Time out and the Date/Time in. Then you can use the select - case to work out not only the duration to skip, but also (optionally) identify if a passed Out or In is not valid.

For help with how you add a function to ACCESS, please look at the following:
https://docs.microsoft.com/en-us/do...guage-reference/statements/function-statement

The simplest version will look like

Function FindTAT(DtOut As Date, DtIn As Date) As Date
    FindTAT = DtOut - DtIn
End Function

More complex is when you do the Select. It starts looking like

Function FindTAT(DtOut As Date, DtIn As Date) As Date
Select WeekDay(DtIn)
   Case 1 
   ..Code here to check if its after 10:30, if so use a function you write to say if it meets your requirement or not
   Case 2 to 5
      FindTAT = DtOut - DtIn
   Case 6
... check if before 5:30pm, if so use a function you write to say it meets your requirements or not
   Case 7
      Call function to state you have an order outside normal time. Should also be called in the if NOT for 1 and 6 when they check their time range.
   END Select
End Function

At the moment I'm not up for working this out fully. Caught a nasty bug and my head is feeling rather... fuzzy. I do hope this gives you a better idea of what it should start to look like, then in your query you would have
"Column: FindTAT( [CompletionDate], [Received_Time])"

 

[Gasman]

The way I read that is

If a ticket comes in at 3pm on a Friday, then 2.5 hours are used up on that Friday. The remaining 21.5 hours will start at 10:30pm on Sunday, so should be completed to meet the SLA by 8pm Monday.?

So as Mark_ has said create a function that passes in the Date and Time in of the ticket and the function passes out the date and time it should be completed by. Then store when it is actually completed.

Am I on the right lines.?

Hey Mark,
Let me elaborate, Lets say i work on IT support and my work window is from 10:30 Pm sunday to 5:30 pm friday, when a ticket comes to system my team have 24 hours TurnAroundTime to solve it, however my team do not work on weekend from 5:30 pm friday till 10:30 pm sunday, so all tickets clock which arrived before 5:30 should stop on 5:30 friday and resume from 10:30 sunday also any ticket comes after 5:30 starts there TAT at 10:30 pm sunday. so i have 2 columns in table one for ticket_in_time and ticket_out_time, i want to calculate TAT for all tickets as i miss their tat or not with weekend condition.

I am trying to do it myself by making some query or formulae however i need it by today as i have a job assessment today so any help would be a blessing.
Thank You.