Slicemahn
05-29-2009, 08:56 AM
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.
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.