time constraints for SLA

[penfold1992]

ive been given a large list of entries from a database which essentially give me 3 dates... (its a db to log issues)

date 1 - issue raised
date 2 - issue passed to my department
date 3 - completed date

so to find out how long we have the issue would be date 3 - date 1 and to figure how long our department spends on an issue would be date 3 - date 2

this is where the issue comes in...
the SLA the department agrees to says that a percentage needs to take less than 3 working days.

They define a working day from 6am to 6pm excluding weekends. So how can I go about setting up a column which does:

(Date 3 - date 1) - (time counted from 6pm to 6am + weekends if necessary)

I currently have this set up,
Date 3 - date 1 = Value
round this value to the nearest whole number (if this is 0, it wasnt left over night... if it is 1 it was left 1 night, 2 2nights and so on)
(date 3 - date 1) - (0.5 x value) because 0.5 is the time (in days) 6pm to 6am is.
however this doesnt detect whether or not a weekend was involved (to shorten the list i can remove all values below 2.5 days as a weekend IS 2.5 days... if it didnt take 2.5 days then it must not have included a weekend)

Thanks for any help because i am honestly stuck on even where to go with this one =(

[NickyC]

hi
do all 3 times and dates fall on a weekday between 6am and 6pm?

[penfold1992]

no, some times dates 1 and 2 fall on weekends. But as far as i know they are all between 6am and 6pm.

[NickyC]

Hi
try this formula
it counts (I think!) the number of working hours between 2 dates, assuming a 12-hour working day (6am to 6pm) Monday to Friday

Please Login or Register  to view this content.

[penfold1992]

NickyC thanks for coming up with that formula, I have a few questions about it...

firstly, would it be possible to explain each phrase in english so i can understand what its doing? (just to make sure its doing what i would like.... im not sure if i explained it properly)

secondly, you have A1 and B1 in that formula but dont state which ones they are? you could use this to explain:
start date = A1
end date = B1
as an example...

Also I notice you use round up and down. If possible, could the value be given as an exact number of hours (even if it goes into decimals) that way i could have a seperate column for rounding the values up or down if needs be...

lastly, will this make my excel slow down? im planning on autofilling a sheet with over 1000 rows with the formula once I can use it.

Thanks for your help, greatly appreciated.

[NickyC]

Hi
Here goes – I always find it hard to explain formulas. I have bolded the bits I think are significant to how the formula works - sorry if this seems like shouting!

It assumes A1 is the start and B1 is the end.

The formula is in 3 parts

The first part calculates the number of whole working days between the start and end dates, but not including those days. This is what the roundup and rounddown part of the formula is for – to ensure it counts only days between those dates. The networkdays formula ensures only week days are counted, not weekends. This is then multiplied by 12 to get the number of working hours (between 6am and 6pm) on those days.

The second part counts the hours on the starting day that fall within the 12-hour working period. It uses Mod(A1,1) to return only the hours part of the time and date in cell A1, not the date value itself. This is expressed as a decimal, e.g. 6am = 0.25. If the start falls before 6am then it counts a full 12 hours for that day; if after 6am but before 6pm it counts the fraction of the day between the start time and the end of the working day; and if after 6pm it returns 0 for that day. This formula is wrapped in an IF condition - IF(WEEKDAY(A1,2)<6 - so that it only returns a value if the date is Monday-Friday. This value is then multiplied by 24 to convert it from a fraction of a day into hours.

The third part performs a similar calculation for the end day, but with the formula reversed so it returns hours between 6am and the end time, not between the start time and 6pm.

The formula returns a numeric value representing the number of working hours between start and end – e.g. 10½ hours = 10.5. The cell this formula is in should be formatted as a number not a date or time, as a date formula will treat each hour as a day.

Hopefully, any number returned that is less than 36 will match your criteria.

I hope this makes sense!

[NickyC]

looks like it needs a tweak for periods less than a full day apart - try this instead:

Please Login or Register  to view this content.

an example -

if start time is 4pm on Wed 18 july and end time is 11am on Mon 23 July then:

the first part returns 24 hours on the two whole working days between the start and end days (Thu and Fri)
the second returns 2 hours worked between 4pm and 6pm on the starting day, Wed
the third returns 5 hours between 6am and 11 am on the end day, Mon

for a total of 31 hours

[penfold1992]

this appears to be the work of genius, I am truely astonished by your excellence good sir!

but yes, it still falls short on values less then a day...

i have 10:50 to 13:30 and it kicks out a value of 14.666 hours when it should be 2.666 meaning it has added a day.


EDIT: also, it is indeed slowing excel down an aweful lot. admittingly the data im using is too large currently however its changed from a 2 minute operation to a 15minute operation and counting =)

EDIT:EDIT: the single days dont matter so much as ill be filtering them out anyway, would be nice to see it working perfect though... Another point to mention is that i am going to be using this in a macro and it churns out some bad results because it changes the code

Please Login or Register  to view this content.

is there anyway to prevent this?

ps:
the code i enter in is:

Please Login or Register  to view this content.

and then i autofill it downwards.

[NickyC]

this version corrects for the error when both dates are on the same day

Please Login or Register  to view this content.

a macro may be quicker as it would insert values not formulas - would you like to try that?

[penfold1992]

certainly, im not too sure as to what you mean but i am open to try anything.
I am trying to use a macro to populate and then handle the data so I dont need to have formulas in there if it will put the values in. Using a macro to fill what you currently gave me isnt working at the moment anyway because it is changing some of the values as i mentioned in my above post.

Again thank you so much

[NickyC]

Hi
I just replied to your message
here is some code to try
it ssumes your start dates are in the range "StartRange" and the end time is in the column to the right of that. It types the hours calcs in successive cells in a column, starting at the "OutCell". Tou will need to change these addresses to suit your data

Please Login or Register  to view this content.

[daddylonglegs]

Hello guys,

I didn't try the macro but your last formula suggestion fails in some circumstances, I think, NickyC - if A1 is a Thursday at 14:00 and B1 2 days later (a Saturday) also at 14:00 then result should be 16 (4 hours remaining on Thursday and a full day on Friday) but formula gives me 4.

This formula should work for any start/end times/dates and can easily be adapted to exclude holidays too

=12*NETWORKDAYS(A1,B1)-12+IF(NETWORKDAYS(B1,B1),MEDIAN(MOD(B1,1)*24,6,18),18)-MEDIAN(NETWORKDAYS(A1,A1)*MOD(A1,1)*24,6,18)

[penfold1992]

I have tried your macro and unfortunately its not working.
I change these values

Please Login or Register  to view this content.

I change F1 to N3 and A1:A67 to B3:B70

The start date is in column B and the end date is in column D.

the macro stops at

Please Login or Register  to view this content.

Also there are a few ' marks suggesting those are comment lines however im not sure if that was intended or not....

thanks again

[penfold1992]

so I have tried again this morning however its still not giving me what i need unfortunately.

The values are in whole numbers when it would be prefered if they gave exact values which i could then later round up if needs be. I am unable to check the numbers are right due to me not knowing where the rounding occured.

[NickyC]

Hi
sorry I've had a lot on with work and study and have not been able to respond until now

this macro puts the values generated by daddylonglegs' formula into cells in column F, assuming your start dates are in columns A and end dates in column B. if you want to use different columns, you may need to adjust the offset values RC[-5] etc in the fomula, is it is comparing values in cells 5 columns to the lef and 4 columns to the left, at present.

Please Login or Register  to view this content.