+ Reply to Thread
Results 1 to 3 of 3

EXCEL: Formula to Determine whether a date is “within Business Hours” or “Outside Business

  1. #1
    Registered User
    Join Date
    06-23-2019
    Location
    Earth
    MS-Off Ver
    365
    Posts
    5

    EXCEL: Formula to Determine whether a date is “within Business Hours” or “Outside Business

    Trying to create a formula(s) for SLA report to show whether an agent acknowledged the assignment within the SLA time frame or not.

    I found a formula in one of the other posts to show office hours are from Mon-Fri ONLY from 8-5:30pm I want to exclude evening, weekends, and holidays. Can it say "after hours" or "weekend" or "holiday" instead of using IN or OUT?

    How to modify the formula to work? I tried other formats as well but it was the length of a novel and it didn't work.

    =IF(AND(WEEKDAY(A2)>1,MOD(A2,1)*24>=8,MOD(A2,1)*24<=IF(WEEKDAY(A2)=7,14,20)
    ),"In","Out")
    The section [7,14,20] was replaced with 17:30 (for 5:30pm)

    A2=start time (DD/MM/YYYY HH:MM)
    B2=end time

  2. #2
    Banned User!
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: EXCEL: Formula to Determine whether a date is “within Business Hours” or “Outside Busi

    Welcome to the forum!

    Excel doesn't have 'holidays' pre-defined, you would need to create a list of these somewhere that the formula can refer to.

    Something like this should do it (not tested). The range, $C$2:$C$10 refers to a list of holiday dates.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    06-23-2019
    Location
    Earth
    MS-Off Ver
    365
    Posts
    5

    Re: EXCEL: Formula to Determine whether a date is “within Business Hours” or “Outside Busi

    Thank you for your reply Jason.b75.
    I just tried the formula and it wroks!
    Brilliant!
    Thank you so much Jason.b75.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 8
    Last Post: 01-31-2016, 10:14 AM
  2. Need formula to determine recommended business hours
    By bswails in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-08-2013, 05:05 PM
  3. Adding business days to a business day formula
    By snappytim in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-29-2013, 07:36 PM
  4. [SOLVED] Determine if a date is a business day or not
    By csh8428 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-09-2012, 04:23 PM
  5. Replies: 5
    Last Post: 02-11-2010, 05:13 PM
  6. Can Excel2002 determine if a calculated date is a business day?
    By jsmahnken in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-26-2005, 06:05 PM

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