I am in way over my head. Here’s the scenario: hours of operation are between 7:00 AM and 4:00 PM.
One type of document is to be scanned within 4 hours, another type by 10:00 AM, the 3rd type within 3 business days.
I need to create the SLA (Service Level Agreement due date and time) for each entry, depending on the type.
One problem I am having is the 4 hour docs can run over to the next day. That’s why =IIf(([Hours]=4),DateAdd("h",4,[MAIL_LOG_TIME])) does not work. Service stops at 4:00 PM. Simply adding 4 hours to may take it beyond 4:00 PM. But the remaining time starts the next day at 7:00 AM.
If documents are logged at, for example 2:30 PM. We have 1 ˝ hrs until 4:00 PM, and 2 ˝ remaining hrs starting at 7:00 AM the next day. If today’s [MAIL_LOG_DATE] and [MAIL_LOG_TIME] is 4/25/2019, 2:30 PM, I know the expected SLA is 4/26/2019, 9:30 AM, but how to calculate that, skipping weekends and holidays, And put in all in a Microsoft Access report text box, I have no clue
Also, there are 3 fields in the table [RECORDS_DATA] for each type: [Hours], always 4, [Specific Time], Always same day 10:00 AM, and [Business Days], always 3 business days from [MAIL_LOG_DATE] & [MAIL_LOG_TIME]. Only one of the three fields is applicable to each type.
So, I guess, a huge nested formula is necessary? Something like…
=IIf(([Hours]=4),DateAdd("h",4,[MAIL_LOG_TIME])),IIf(([Specific Time]="10:00:00 AM","10:00:00 AM",))IIf(([Business Days]=3),DateAdd("d",3,([MAIL_LOG_DATE] & " " & [MAIL_LOG_TIME])))
…but how to deal with weekends and holidays, still no clue. Can anybody help?