I'll have to look at it and figure out how to calculate it differently. I'll get back to you soon. Paul ----------------------------------------- “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley -----------------------------------------
________________________________ From: Timothy Kluck <timkl...@gmail.com> To: excel-macros@googlegroups.com Sent: Mon, December 17, 2012 10:02:59 AM Subject: Re: $$Excel-Macros$$ Calculate Business Hours exluding weekends and holidays with a caveat That must be it! I think it's because your weekday formula "return_type" number (16) doesn't work in Excel 2007. I have the option of doing: 1 Returns a number from 1 (Sunday) to 7 (Saturday). 2 Returns a number from 1 (Monday) to 7 (Sunday). 3 Returns a number from 0 (Monday) to 6 (Sunday). When I replace the formula with either 1, 2, or 3 - I receive a value - albeit an incorrect value. Do you know which "return_type" I should use and which corresponding formula (i.e. >= 3, or <=5, etc.) I should enter? Thanks again for your help! V/R, Tim On Sunday, December 16, 2012 11:45:11 AM UTC-5, Paul Schreiner wrote: Are you using Excel 2007 or Excel 2010? > >I'm using Excel2010. >When I first open your files, I see the "#NUM!" values. > >But when I click the "edit" button, the errors go away and the values appear. > > >Paul >------------------------------ ----------- >“Do all the good you can, >By all the means you can, >In all the ways you can, >In all the places you can, >At all the times you can, >To all the people you can, >As long as ever you can.” - John Wesley >------------------------------ ----------- > > > > > ________________________________ From: Timothy Kluck <timk...@gmail.com> >To: excel-...@googlegroups.com >Sent: Fri, December 14, 2012 4:19:45 PM >Subject: Re: $$Excel-Macros$$ Calculate Business Hours exluding weekends and >holidays with a caveat > >Paul, > >I can't thank you enough for working so hard on this. I definitely understand >your logic but unfortunately when I try to create the Start (adj) column, it >returns the error #NUM!. What is weird is that the End(adj) column works fine. >Do you have any idea why this would happen? I have attached my spreadsheet. > >Thanks again for being so on top of this. > >V/R, >Tim > > > >On Friday, December 14, 2012 3:36:07 PM UTC-5, Paul Schreiner wrote: >Timothy, >> >>OK, I played with it a while, using formulas. >>I'm not sure it you'll be able to manage it, or even follow along (I have >>trouble, and I WROTE IT) >> Most of the columns you don't need. >>Just the original start/end times >>the adjusted start/end times >>and the Hours. >> >>The rest is for trying to develop the logic and visually SEE the data >>represented. >> >>Here's the logic: >> >>First, I checked the time of day for the entries. >>For the start time: >>If it was before 6:00am, I adjusted it to 6:00am of the same day. >>If it was after 9:00pm, I adjusted it to 6:00am of the FOLLOWING day. >> >>Next, I checked to see if the resulting day was on a Saturday or Sunday. >>If it was, I adjusted it to the following Monday. >> >>One issue here that I haven't been able to account for is if the start day >>was >>actually on a Holiday. >>I haven't figured out how to adjust for that one... >> >>For the End time, the adjustments were similar except that: >>If the End time was after 9:00pm, I adjusted it to 9:00pm of the same day. >>If the End time was before 6:00am, I adjusted it to 9:00pm of the previous day. >> >>These new date/times I then used Networkdays to determine the number of >>working >>days between the adjusted dates. >>then, I removed any partial days (hours after 6:00am and before 9:00pm) >> >>I left several sample data records in place for testing purposes... >> >>So far, it LOOKS ok, but you'll have to be the judge. >> >>I think if we need to do anything further with the logic, I'll go ahead and >>develop a UDF. >> >>Paul >>------------------------------ ----------- >>“Do all the good you can, >>By all the means you can, >>In all the ways you can, >>In all the places you can, >>At all the times you can, >>To all the people you can, >>As long as ever you can.” - John Wesley >>------------------------------ ----------- >> >> >> >> >> ________________________________ From: Timothy Kluck <timk...@gmail.com> >>To: excel-...@googlegroups.com >>Sent: Fri, December 14, 2012 11:08:13 AM >>Subject: Re: $$Excel-Macros$$ Calculate Business Hours exluding weekends and >>holidays with a caveat >> >>Paul, >> >>Thanks for getting back so quickly. For Row 11, that was my error, it was >>suppose to end the following day at 11:00 AM (giving us a total of 5 Business >>Hours). I have attached the revised spreadsheet. >> >> >>Perhaps a little more explanation on my part can help. I am trying to measure >>a >>Help Desk Service Provider and their Resolution Time for a call request. >> >>The Start Time (in the attached file) is when a Help Desk call is answered >>and >>the End Time (in the attached file) is when the Help Desk call request is >>resolved. Unfortunately, the contract for the service provider is measured in >>Business Hours (from 6:00 AM - 9:00 PM, excluding weekends and holidays). So >>that means if a call didn't take place during any type of business hour >>(between >>the Start Time and End Time), we have to disregard that call completely from >>any >>performance calculation. >> >> >>So, if a call came in on Saturday 11/17 at 8:30 AM and was completed on >>Saturday >>11/17 at 11:30 PM, we would have to disregard the entire call from any >>performance calculation (i.e. the Business Hours for that call would be "N/A" >>or >>"Null"). But if a call came in on Saturday 11/17 at 8:30 AM and was completed >>on >>Monday 11/19 at 8 AM, the call would have included 2 Business Hours (i.e. >>from >>6:00 AM on Monday through 8:00 AM on Monday). >> >> >>As for how to proceed, ideally I would like to use excel formulas but if it >>can't be done - we could try VBA. I am not familiar with it, but since I need >>to >>complete this project, I will try my best to follow any instruction you have. >> >>Thanks again and I really appreciate your help! >> >>Tim >> >>On Friday, December 14, 2012 10:45:10 AM UTC-5, Paul Schreiner wrote: >>Timothy, >>>What you're asking is pretty tricky... >>>----------- >>>TECHNICALLY, you cannot START an action on a Saturday and END it on a Sunday >>>of >>>the SAME WEEK, >>>unless you "assume" that the week begins on Monday (instead of Sunday) >>>And... for your row 11, the start time is AFTER the end time. >>>----------------------- >>> >>>Networkdays does a nice job of counting the working days (less holidays) >>>between >>>two dates. >>>But it gives the result in whole days, not partial days. >>>so, in the date range in which only 2 hours is within the Business Hours, it >>>still calls it (1) "working day". >>> >>>I think, with all of the criteria you have to meet, you're going to have to >>>create a "user defined function". >>>(VBA macro) >>> >>>If you don't want that approach, then you're probably going to have to add >>>several columns. >>>(1) with networkdays * BusHours/Day >>>another that checks to see if the start date is a weekend or holiday and use >>>this to subtract from the busHours. >>> >>>Same thing with the end time. >>> >>>We MIGHT be able to come up with something.. but it's gonna be ugly... >>> >>> >>>How do you want to approach this? >>> >>>Paul >>>------------------------------ ----------- >>>“Do all the good you can, >>>By all the means you can, >>>In all the ways you can, >>>In all the places you can, >>>At all the times you can, >>>To all the people you can, >>>As long as ever you can.” - John Wesley >>>------------------------------ ----------- >>> >>> >>> >>> >>> ________________________________ From: Timothy Kluck <timk...@gmail.com> >>>To: excel-...@googlegroups.com >>>Sent: Fri, December 14, 2012 9:57:59 AM >>>Subject: $$Excel-Macros$$ Calculate Business Hours exluding weekends and >>>holidays with a caveat >>> >>>Hi all, >>> >>>I have been really struck trying to find a formula to calculate business >>>hours >>>between 2 dates, especially if it is started on a weekend and ending on a >>>weekend. >>> >>>I have attached an excel spreadsheet with certain dates, and the answers >>>that I >>>am looking for. I have also attached a holiday list and the workday start >>>(6:00 >>>AM) and work day end (9:00 PM). >>> >>> >>>If an action is started on Saturday and ended on Sunday (of the same week), >>>I >>>want the calculation to return "N/A" (or something that I can identify that >>>should not be considered when measuring performance). However, if an action >>>is >>>started on a Saturday and ended the following Saturday (without any holidays >>>in >>>the middle), I want the calculation to return 75.00 (business hours). >>> >>> >>>Essentially I want to calculate elapsed business hours between two dates >>>while >>>disregarding weekends, holidays, and "after-hours" AND if it turns out that >>>NO >>>business hours took place during those two dates, I want the calculation to >>>return an easily identifiable text field like "N/A" for example. ALSO, and >>>this >>>is important, if an action was started and ended at the exact same time >>>(WITHIN >>>NORMAL WORKING HOURS), I need the calculation to return 0.0 and NOT "N/A". >>> >>> >>>I've included a few other examples in my spreadsheet that have stumped me. I >>>can >>>find a calculation for 1 of them, but yet to find 1 calculation for ALL of >>>them. >>> >>>Hope I was clear, if not please feel free to ask any follow-up questions. >>> >>>I REALLY APPRECIATE YOUR HELP! >>> >>>-- >>>Join official Facebook page of this forum @ https://www.facebook.com/ >>>discussexcel >>> >>>FORUM RULES >>> >>>1) Use concise, accurate thread titles. Poor thread titles, like Please >>>Help, >>>Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not >>>get >>>quick attention or may not be answered. >>>2) Don't post a question in the thread of another member. >>>3) Don't post questions regarding breaking or bypassing any security measure. >>>4) Acknowledge the responses you receive, good or bad. >>>5) Jobs posting is not allowed. >>>6) Sharing copyrighted material and their links is not allowed. >>> >>>NOTE : Don't ever post confidential data in a workbook. Forum owners and >>>members >>>are not responsible for any loss. >>>--- >>>You received this message because you are subscribed to the Google Groups >>>"MS >>>EXCEL AND VBA MACROS" group. >>>To post to this group, send email to excel-...@googlegroups.com. >>>To unsubscribe from this group, send email to excel-macros...@ >>googlegroups.com. >>>Visit this group at http://groups.google.com/ group/excel-macros?hl=en. >>> >>> >>>-- >Join official Facebook page of this forum @ https://www.facebook.com/ >discussexcel > >FORUM RULES > >1) Use concise, accurate thread titles. Poor thread titles, like Please Help, >Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get >quick attention or may not be answered. >2) Don't post a question in the thread of another member. >3) Don't post questions regarding breaking or bypassing any security measure. >4) Acknowledge the responses you receive, good or bad. >5) Jobs posting is not allowed. >6) Sharing copyrighted material and their links is not allowed. > >NOTE : Don't ever post confidential data in a workbook. Forum owners and >members >are not responsible for any loss. >--- >You received this message because you are subscribed to the Google Groups "MS >EXCEL AND VBA MACROS" group. >To post to this group, send email to excel-...@googlegroups.com. >To unsubscribe from this group, send email to excel-macros...@ googlegroups.com. >Visit this group at http://groups.google.com/ group/excel-macros?hl=en. > > >-- Join official Facebook page of this forum @ https://www.facebook.com/ discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To post to this group, send email to excel-...@googlegroups.com. To unsubscribe from this group, send email to excel-macros...@ googlegroups.com. Visit this group at http://groups.google.com/ group/excel-macros?hl=en. -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en. -- Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros?hl=en.
