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.
Business Hours Example.xlsx
Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
