I use Networkdays formula to calculate difference bwtween 2 dates,if the end
date is beyond 12 hours the formula returns 1, so my calculation of SLA is
going vague.
Is there any formula that we can use which will exclude weekdays and will
report in hours. Or is it possible in vba to do this cutom requirement

If you specify your requirement in more details, start time, end time, any
excluded breaks etc., I am sure we can come up with something.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Thomas T" <Thomas wrote in message
...
I use Networkdays formula to calculate difference bwtween 2 dates,if the
end
date is beyond 12 hours the formula returns 1, so my calculation of SLA is
going vague.
Is there any formula that we can use which will exclude weekdays and will
report in hours. Or is it possible in vba to do this cutom requirement

Hi BOB

start time is 21/02/2008 22:47:22
end time is 22/02/2008 15:19:10

if i use the formula

=NETWORKDAYS(F1,G1) where F1 is start time and G1 is end time, the result is
2 eventhough the time is within 24 hours.
as a matter of fact anything beyond 12 hours is rounded off as next day and
give me 2, so my SLA calculation is tossed off once any case like this
arises, ie, anything beyond 12 hours will be reported out of SLA,

At the same time I cant use the normal day diffeence as weekends are
excluded for SLA calculations

**Any help is appreciated, Bob

clearly it is within 24 hours but

"Bob Phillips" wrote:

If you specify your requirement in more details, start time, end time, any
excluded breaks etc., I am sure we can come up with something.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Thomas T" <Thomas wrote in message
...
I use Networkdays formula to calculate difference bwtween 2 dates,if the
end
date is beyond 12 hours the formula returns 1, so my calculation of SLA is
going vague.
Is there any formula that we can use which will exclude weekdays and will
report in hours. Or is it possible in vba to do this cutom requirement

Thomas,

Normally with problems like this, the OP wants to count say time between
8:00 and 5:00PM as a whole day, and add in the appropriate hours between
those times as days and hours.

It sounds to me that you only want to know the number of days. Is that a
correct assessment? And if so, should your example return 1 or 0, that is do
we include a part day as a day or not.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Thomas T" wrote in message
...
Hi BOB

start time is 21/02/2008 22:47:22
end time is 22/02/2008 15:19:10

if i use the formula

=NETWORKDAYS(F1,G1) where F1 is start time and G1 is end time, the result
is
2 eventhough the time is within 24 hours.
as a matter of fact anything beyond 12 hours is rounded off as next day
and
give me 2, so my SLA calculation is tossed off once any case like this
arises, ie, anything beyond 12 hours will be reported out of SLA,

At the same time I cant use the normal day diffeence as weekends are
excluded for SLA calculations

**Any help is appreciated, Bob

clearly it is within 24 hours but

"Bob Phillips" wrote:

If you specify your requirement in more details, start time, end time,
any
excluded breaks etc., I am sure we can come up with something.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Thomas T" <Thomas wrote in message
...
I use Networkdays formula to calculate difference bwtween 2 dates,if the
end
date is beyond 12 hours the formula returns 1, so my calculation of SLA
is
going vague.
Is there any formula that we can use which will exclude weekdays and
will
report in hours. Or is it possible in vba to do this cutom requirement

Oh, and how many days would

21/02/2008 22:47:22 to 22/02/2008 23:19:10

come out as.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Thomas T" wrote in message
...
Hi BOB

start time is 21/02/2008 22:47:22
end time is 22/02/2008 15:19:10

if i use the formula

=NETWORKDAYS(F1,G1) where F1 is start time and G1 is end time, the result
is
2 eventhough the time is within 24 hours.
as a matter of fact anything beyond 12 hours is rounded off as next day
and
give me 2, so my SLA calculation is tossed off once any case like this
arises, ie, anything beyond 12 hours will be reported out of SLA,

At the same time I cant use the normal day diffeence as weekends are
excluded for SLA calculations

**Any help is appreciated, Bob

clearly it is within 24 hours but

"Bob Phillips" wrote:

If you specify your requirement in more details, start time, end time,
any
excluded breaks etc., I am sure we can come up with something.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Thomas T" <Thomas wrote in message
...
I use Networkdays formula to calculate difference bwtween 2 dates,if the
end
date is beyond 12 hours the formula returns 1, so my calculation of SLA
is
going vague.
Is there any formula that we can use which will exclude weekdays and
will
report in hours. Or is it possible in vba to do this cutom requirement

Hi Bob

Sorry for the delay in reply..

This would be 2,which is correct

but the problem can be explained better, if we take another example,
start time is 21/02/2008 10:47:00 PM
end time is 22/02/2008 9:19:00 PM

clearly it is within 24 hours, but networkdays will show it as 2nd day,

I think the problem is that networkdays(A1,A2) is rounding off beyond 12
hours into the next day..
so getting 2 instead of 1 and my happiness is tossed.....

TT

hi

any update on this, i m still left without clue , Bob u ther?

Rgds

TT

"Thomas T" wrote:

Hi Bob

Sorry for the delay in reply..

This would be 2,which is correct

but the problem can be explained better, if we take another example,
start time is 21/02/2008 10:47:00 PM
end time is 22/02/2008 9:19:00 PM

clearly it is within 24 hours, but networkdays will show it as 2nd day,

I think the problem is that networkdays(A1,A2) is rounding off beyond 12
hours into the next day..
so getting 2 instead of 1 and my happiness is tossed.....

TT

hi all

found the solution

IF(((VALUE(RIGHT(L2,9))*1440/60)-(VALUE(RIGHT(H2,9))*1440/60))=0,NETWORKDAYS(L2,H2)-1,NETWORKDAYS(L2,H2))

exception is for 1 day where i used an if condition to give 1,

Regards

TT
"Thomas T" wrote:

hi

any update on this, i m still left without clue , Bob u ther?

Rgds

TT

"Thomas T" wrote:

Hi Bob

Sorry for the delay in reply..

This would be 2,which is correct

but the problem can be explained better, if we take another example,
start time is 21/02/2008 10:47:00 PM
end time is 22/02/2008 9:19:00 PM

clearly it is within 24 hours, but networkdays will show it as 2nd day,

I think the problem is that networkdays(A1,A2) is rounding off beyond 12
hours into the next day..
so getting 2 instead of 1 and my happiness is tossed.....

TT