Network days rounding off hours

[Thomas T]

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

 

[Bob Phillips]

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]

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]

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)

 

[Bob Phillips]

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]

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

 

[Thomas T]

hi

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

Rgds

TT

 

[Thomas T]

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