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 <timkl...@gmail.com> To: excel-macros@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-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.
