Paul,
Try this (I have given it a test, but your data is bound to be better)
Assuming the incident date/time in A2 such as dd/mm/yyyy hh:mm:ss, and the
SLA time in B1 in hours (make sure this is formatted as [h]:mm to allow
more than 1 day), e.g. 04:00, 48:)0, then in B2
=IF(DAY($A2+B$1)=DAY($A2),$A2+B$1,$A2+B$1+(WORKDAY ($A2,1)-INT($A2)-1))
--
HTH
RP
(remove nothere from the email address if mailing direct)
"Paul Martin" wrote in message
om...
Hi all
I need to ascertain when a given action needs to be completed by, by
adding a SLA (service level agreement) to the current date and time.
Note:
- SLAs may be anything from 1 minute to 2 weeks
- Weekends must be ignored (not included in calcs)
- A work day is Monday to Friday 6.30AM to 7.00PM
So, for example, if it's Friday 4PM and a SLA is 4 hours, the required
completion time should be Monday 7.30AM.
I've looked at Chip Pearson's site for date/time calculation, but my
problem does not appear to be addressed. Any direction is much
appreciated.
Paul Martin
Melbourne, Australia
|