 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / March 2008Network days rounding off hours
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.  Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) >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 > 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. [quoted text clipped - 5 lines] > > 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. Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Hi BOB > [quoted text clipped - 30 lines] >> > 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. Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Hi BOB > [quoted text clipped - 30 lines] >> > 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 > Hi Bob > [quoted text clipped - 13 lines] > > 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 > hi > [quoted text clipped - 21 lines] > > > > TT |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.