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 <javascript:>> > *To:* excel-...@googlegroups.com <javascript:> > *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 <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<http://groups.google.com/group/excel-macros?hl=en> >>> . >>> >>> >>> >> -- >> Join official Facebook page of this forum @ https://www.facebook.com/ >> discussexcel <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<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<javascript:> > . > To unsubscribe from this group, send email to > excel-macros...@googlegroups.com <javascript:>. > 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.
