Add Time to a TimeStamp?
I work for a customer service organization. People call in. The initial intake gets posted to a list. We have to call them back within three hours (call back 1). Then, if we don't reach them, we have to call them back twice more (CB 2 and CB 3) at four hour intervals. When I create the record, it is timestamped with date and time. Depending on what is in the field indicating that action that has just been taken (initial intake, CB 1, 2 or 3), I need to add either three or four hours to the TimeStamp on that record.
Ah, but it's not that simple. If the call will then fall after 5:00 pm, we kick it over to 8:30+ the following morning. So a call back that would schedule for 7:30 at night (2.5 hours after 5:00 pm) ends up scheduling for 10:00 am the next day (2.5 hours after 8:30 a.m.).
And, of course, it has to know to skip Saturdays and Sundays.
I've done all this in a spreadsheet except for the automatic time stamp. And the spreadsheet is quickly getting huge. And I'm having to keep the notes on each action (each phone call and other actions--they might phone in, we might send a letter, etc.) all on one line in the spreadsheet, so it's not really going to work.
So, let's start with how do I add 4 hours to the time stamp. I am using the Case statement like this:
Case ( Action_Time < "13:00" ; Action_Time + "04:00:00")
and it's not working. It's adding two days and four seconds. Once I solve the problem of adding time, I'll worry about weekends, then I'll tackle holidays.
Time is stored in a timestamp field in seconds. If you want to add time to the original time in terms of hours, add the correct multiple of 3600 seconds for each hour.
Action_Time + ( 3600 * 4 )
will compute a timestamp 4 hours in the future from the time in Action_Time.