Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How To Refresh Chart Data Without Calculating The Worksheet | Charts and Charting in Excel | |||
Calculating Interest where rate changes per quarter | Excel Discussion (Misc queries) | |||
Calculating tax Formula | Excel Discussion (Misc queries) | |||
Formulas stop calculating at random times when editing a few spreadsheets. | Excel Discussion (Misc queries) | |||
Calculating without including Hidden Cells | Excel Discussion (Misc queries) |