Time between two dates

[techstyle20]

Hi,

I need to calculate an SLA.

I have 1 column called 'Date/Time Requested' containing the time and the date together, as in:

dd/mm/yy hh:mm

I also have a second column called 'Date/Time Completed' containing the date and time in the same format as above.

I need a macro that compares the two and works out the following:

< 48 hours = "Within SLA"
> 48 hours = "Outside SLA"

I did get the following answer on here:

=IF((L2-E2)*24<48,"Within SLA","Outside SLA")

This works perfectly however it does not take into account weekends and holidays. I presume I would have to set up some kind of table to mark the weekends and holidays.

 

[cyrilbrd]

Hi,

Could date completed fall during weekend or VL?
If yes new interval should be date requested till next workday?

What would be the Cut-Off Time?

 

[techstyle20]

Hi,

No, date completed or requested can only occur on a working day so if requested on a Friday at 13:00 it will still be within SLA until Tuesday at 12:59.

 

[cyrilbrd]

ok, so =IF((L2-E2)*24<48,"Within SLA","Outside SLA") works fine right? you just want a code to do this computation instead of the formula?

 

[techstyle20]

No No,

Sorry if I am confusing. This code does not take into account weekends and holidays which I need it to.

It does not matter whether this is a function or VBA.

Thanks.

 

[techstyle20]

I have been looking at the NETWORKDAYS function but I cannot download the additional add in in work.

 

[cyrilbrd]

ok, it's cool, could you post a sample of a few dates and what result you should get?
use PHP wrap or check this link.

 

[cyrilbrd]

techstyle20,

I have a proposal using index and using a database but I'll wait to see your sample in order to confirm that my method would work. It would only require one sheet for the date's list.

 

[techstyle20]

Thanks,

SLA=48h

1.
Date/Time Requested:
02/05/12 12:00

Date/Time Completed:
04/05/12 11:59

Expected answer:
Within SLA

2.
Date/Time Requested:
02/05/12 12:00

Date/Time Completed:
04/05/12 12:01

Expected answer:
Outside SLA

3. (Includes Weekend)
Date/Time Requested:
04/05/12 12:00

Date/Time Completed:
07/05/12 12:00
Within SLA

I hope this makes sence. Thanks very much for your help. Its no problem if you dont answer as my boss has moved the goalposts and we no longer need this in the spreadsheet but I would be interested for future reference.

Thanks again.

 

[cyrilbrd]

Given in Sheet1:

date/time requested							date/time completed	
01/Jan/12 14:00							03/Jan/12 13:59	Within SLA
01/Jan/12 13:00							04/Jan/12 15:00	Outside SLA
02/May/12 12:00							04/May/12 11:59	Within SLA
02/May/12 12:00							04/May/12 12:01	Outside SLA
04/May/12 12:01							07/May/12 12:01	Within SLA
07/May/12 12:01							09/May/12 12:00	Within SLA

Created a new sheet and name it DB
in DB create a list as follows:

status	date	newdate
VL	1-Jan	31-Dec
	2-Jan	31-Dec
	3-Jan	3-Jan
	4-Jan	4-Jan
	5-Jan	5-Jan
	6-Jan	6-Jan
	7-Jan	7-Jan
VL	8-Jan	7-Jan
	9-Jan	7-Jan

File with the DB attached in this link. Run Antivirus prior to opening!


Back to sheet1:
with date/time requested in E1 and dates in E2 down
with date/time completed in L1 and dates in L2 down
in M2 type:
=IF((IFERROR(INDEX(newdate,MATCH(TRUNC(L2),date,0)),"")+(L2-INT(L2))-E2)*24<48,"Within SLA","Outside SLA")
Enter.
copy down till needed.