Results 1 to 2 of 2

Calculate Business Hours exluding weekends and holidays with a caveat

Threaded View

  1. #1
    Registered User
    Join Date
    12-12-2012
    Location
    Washington, DC
    MS-Off Ver
    MS Office 365
    Posts
    2

    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 to 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!

    Teamdob
    Attached Files Attached Files
    Last edited by teamdob; 12-12-2012 at 05:32 PM. Reason: typos

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1