Creating Timestamps

[Slicemahn]

Hello Everyone,

I am trying to figure out the best way to update about 5K rows based on date and time. It is a bit complex , so bear with me:

Tickets/cases need to be resolved within 4 hours. I am trying to create the timestamps for the due date/time. What I have been doing is taking a cell and then adding by (=A4 + TIme(4,0,0) which works.

The twist comes in when it is outside of hours, Sunday or when there is not enough time in the day to complete the ticket. That is, closing time is 9 PM and a ticket comes in at 8:59 PM.

The operating hours are as follows: MON - FRI 8 am - 9 PM. Sat 8 - 6 PM.
SUN -Closed.

What I been doing is the following:

IF the ticket is created MON - FRI within operating hours and before 5 PM then TICKETCASE Time + Time(4,0,0).

If the ticket is created MON - FRI within operating hours and before 9 PL then TiCKETCASE TIME + Time(15,0,0).

However if the TICKETCASE TIME is after hours on a MON - FRI then the due date would be the next day's start + 4 hours.

Saturdays are the same except we want to use 2PM as our cutoff date for tickets to be resolved within the same day.
Anything after 2 PM would need to resolved on the Monday and what I used for this is TICKETCASE + Time(18,0,0)+1 and it seems to work.

My challenge is writing the code that would

a) identify the ticketcase as within operating hours or not.
b) writing the code in VBA that would add the proper time to the ticket case date to yield the results.

I would appreciate anyone who would have a way in which I can save 3 hours work!

Many thanks.

[Bob Phillips]

How about using formulae?

[mdmackillop]

A UDF solution
Enter as =MYTIME(A1)

[VBA]
Function MyTime(Data As Range)
Dim Dy As Long, Hr As Single
Dy = Int(Data)
Hr = Data - Dy - 0.0000001
Select Case Weekday(Data)
Case 1
MyTime = Dy + 36 / 24
Case 2 To 6
Select Case Hr
Case Is <= 9 / 24
MyTime = Dy + 12 / 24
Case Is <= 17 / 24
MyTime = Data + 4 / 24
Case Is <= 21 / 24
MyTime = Data + 15 / 24
Case Else
MyTime = Dy + 36 / 24
End Select
Case 7
Select Case Hr
Case Is <= 8 / 24
MyTime = Dy + 12 / 24
Case Is <= 14 / 24
MyTime = Data + 4 / 24
Case Is <= 18 / 24
MyTime = Data + 42 / 24
Case Else
MyTime = Dy + 60 / 24
End Select
End Select
End Function

[/VBA]

[Bob Phillips]

Just for the heck, here is my formula

=A4+TIME(4,0,0)+(WEEKDAY(A4,2)<6)*(HOUR(A4-TIME(0,0,1))>=17)*(TIME(11,0,0))+(WEEKDAY(A4,2)=6)*(HOUR(A4-TIME(0,0,1))>=14)*(1+TIME(14,0,0))

[Slicemahn]

Hi,
Thanks for the response. As you may see the data or timestamps are really based on two factors: the day of the week and the time. If a formula can be used then great but it seems like either a UDF or a Sub proc would solve this.
I see your formula is posted here: This formula would work on all days except Sunday? Is this an array formula? Where if condition 1 * condition 2 * condition 3 * Condition 4 * condition n all need to be satisfied before the appropriate time is added. If you have the time could you briefly explain how this formula works.

[Bob Phillips]

No it is not an array. I assumed that Sunday is immaterial, because it is not a workday no jobs would be posted that day.

It works by adding 4 hours on irrespectively, then checks if it is a weekday and 4 hours goes over so add another 11, if it is Saturday and goes over add another 38.