Calculating date/time
 

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

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