Calculating Hours

  • Thread starter Thread starter dcronje
  • Start date Start date
D

dcronje

Hi,

I have fairly complicated problem which I will try and explain and any
help will be appreciated.

I am trying to determine two things: firstly number of hours to resolve
an event and then number of periods.

I receive a table that tracks events/failures. I get the date and time
that an event is reported and I get the date and time it was resolved.
I need to calculate the number of working hours it has taken to resolve
with working hours being 7:30 to 18:30 everyday except weekends and
holidays. So for example on 3 Feb at 10:30 a problem was reported and
by 5 Feb at 15:30 the problem was resolved. I need to know that the
problem was resolved in 27 hours. (If the problem occurred on Friday at
15:30 and resolved on Monday 9:30 it would be 5 hours)

A B C
Reported Resolved Time Taken
3/2/04 10:30 5/2/04 15:30 ??????????


How can I do this?

Secondly I need to calculate similar thing using the above example I
need to calculate number of periods, basically periods are broken down
into 07:30 to 12:30 is one period and 12:30 to 18:30 is another so from
the information above it would be 6 periods.

Can anyone help? hopefully i have explained enough if not I can answer
any questions

Thanks
 
Hi
to calculate the hours used for resolving try the following formula:
=(NETWORKDAYS(A1,B1)*11)/24-(A1-INT(A20)-TIME(7,30,0))-(TIME(18,30,0)-(
B1-INT(B1)))
(Assumption: A1 and B1 always >=07:30 and <=18:30)
Additional note: To take holidays into account add the third paraméter
of NETWORKDAYS (see Excel helpfile)

For your periods: You have different lengths of periods? (5 hours and 6
hours)? You have to explain your periods calculation a little bit more.
How do you count starting at 10:30 for the first period, etc.

HTH
Frank
 
Thank you for the reply.

When it comes to periods, it is a tricky one.

Basically what happens each type of event has a certain number of
days/hours to be resolved.


If, for example, a certian event has two days to be resolved and the
event is resolved in two days it would count as one occurance if it had
taken three days it would have counted as two occurances because the
minute it goes over the specified period it counts as another occurance
so if it had taken 5 days it would count as three occurances.

Similarly, some events have for example 1 hour to resolve but instead
of having an occurance every hour what happens is if the event occured
at 8:30 the next time it would be counted as an occurance if it had not
been resolved would be at 12:30 and again the following day at 07:30
etc.

If for example the event happened at 12:15 given the hour to resolve
would be 13:15 in this case the next occurance would be at 07:30 the
next day.

So that is how the periods work. Hopefully I have explained it better.

Also what happens is each event is given a code by a user on another
sheet is the table with the data, eg "A" contains the type for the
lookup and "B" contains the allowable time to resolve.

Thanks for the help so far.
 
Hi
interesting SLA agreements you created :-). I think it could be done
though the formulas would get quite complicated due to your different
period lengths. It would be much easier (IMO) if you can change your
period times to the same duration time
1. Period: 07:30 - 13:00
2. Period: 13:00 - 18:30

This way you could just simply divide the calculated resolving time by
5:30 (+ sum calcualtion if to add 1 to this number or not, depending on
the starting time). Using the defined resolving time, measured in
periods (rounded up to the next integer) you can calcualte the number
of occurences (just to give you an idea, not tested)

So my suggestion would be: Change the period times a little bit and
life would be much more easyier.

Frank
 
Unfortunately, the times can not be changed as they have been agreed.

I will give the times ago and test how sensitive it is to variou
occurances but in theory it should be relatively accurate in number o
occurances.

Thanks for the help so far.

I will most probably need more help when bringing in the money value o
each occurance especially when ratcheting
 
Hi Frank
Need ur advice on the following queries for work hours excluding sa
and sun,
1. to calculate start and end time of a case log within work hours

Case time
A1: 24/01/2004 00:11:27 - start case time
B1: 24/01/2004 00:24:14 - end case time

working hours
C1: 08:00:00 - start day
D1: 19:00:00 - end day

Exclude
Saturday and Sunday ( in excel i used holidaylist)

The challenge i face here is not on the excluding sat and sun formula
but on the rules that metrics are used by 2 different support group

1. Helpdesk ( 24 by 7 support include weekends)
2. Desktop ( 8-7pm excluding weekends)

the ideal rules will be exclude the week-end if neither case creatio
nor case closure is on saturday/sunday to cater for helpdesk needs bu
at the sametime justify for desktop,additionally we also need to verif
is that if a case is created on a saturday and closed on a sunday th
ETTR shouldnt be 0 for helpdesk is working on 24 by 7 basis but deskto
are not

I have run out of wits to resolved this and hope that experts withi
this forum can advice and enlighten me and million thks on thi
 
Hi Frank
Need ur advice on the following queries for work hours excluding sat
and sun,
1. to calculate start and end time of a case log within work hours

Case time
A1: 24/01/2004 00:11:27 - start case time
B1: 24/01/2004 00:24:14 - end case time

working hours
C1: 08:00:00 - start day
D1: 19:00:00 - end day

Exclude
Saturday and Sunday ( in excel i used holidaylist)

The challenge i face here is not on the excluding sat and sun formulas
but on the rules that metrics are used by 2 different support group

1. Helpdesk ( 24 by 7 support include weekends)
2. Desktop ( 8-7pm excluding weekends)

the ideal rules will be exclude the week-end if neither case creation
nor case closure is on saturday/sunday to cater for helpdesk needs but
at the sametime justify for desktop,additionally we also need to
verify is that if a case is created on a saturday and closed on a
sunday the ETTR shouldnt be 0 for helpdesk is working on 24 by 7
basis but desktop are not

I have run out of wits to resolved this and hope that experts within
this forum can advice and enlighten me and million thks on this
Click to expand...


Hi
honestly I would suggest you should change to a dedicated Call/Problem
tracking system dedicated for call/helpdesk support (Seeing your
current requirements I can imagine what comes nexts: Reports for each
support group+total, average resolving time, status of open issues,
escalation procedures, flagging overdue entries, automatic reporting
for your customer, etc.). Of course this won't be easier but these kind
of tools include some stadard functionality <vbg>

But if you have to stick to Excel I think you should now enter the VBA
environement :-)
Though this can be achieved with worksheet functions (and some/many
helper columns) I think you need at least some coding for this (the
cases are getting complicated and your calculations are complex). So of
course if you can specify all rules a VBA UDF could achieve this.

Frank
 
Hi Frank

Thks for the reply and infact we are using ARMDOC clarify systems an
the systems do have the calculation provided the vendor but are al
hidden agenda which is why i want to extract their data and run a tria
to verify if the data given by them is geninuely true

i have no experience in VBA and if u can advice me where i can researc
more on this or probably if u can advice me will be grateful, infac
this is a trial test scenario that we need to verify last 2 month
metrics against a manual calculation

thk
 
Hi Frank
Thks for the reply and infact we are using ARMDOC clarify systems and
the systems do have the calculation provided the vendor but are all
hidden agenda which is why i want to extract their data and run a
trial to verify if the data given by them is geninuely true
Click to expand...

Now this I can understand <vbg>:
Better to check your vendor!

i have no experience in VBA and if u can advice me where i can
research more on this or probably if u can advice me will be
grateful, infact this is a trial test scenario that we need to verify
last 2 months metrics against a manual calculation
Click to expand...

A website to start with VBA:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

If you like, send me a detailed description of your business logic and
some sample data to my private mail (frank[dot]kabel[at]freenet[dot]de)
and I'll look if I can create a basic scenario for you

Frank
 
Back
Top